-1

While writing a VBA macro I have tried to select a certain cell by storing both the column letter and the row number separately then putting them together with range(X + Y).select however it is not accepting the number half of it (it does work if I just manually type the number that's stored). Is there a certain way I need to store the number or write it for it be accepted?

I'm getting Type Mismatch in the debug so I'm assuming I've used the wrong word somewhere for something.

Teaching myself excel VBA currently so apologies if it's an easy error that I have just missed.

A simplified version without the extra fluff like named sheets ect. basically looks like below

Dim NVCurrRN As Integer
Dim Prod As Variant

Prod = "B"

Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.Offset(1, 0).Select
NVCurrRN = ActiveCell.Row
Range(Prod + NVCurrRN).Select

When looking at it while debugging Prod = "B" correctly and NVCurrRN = 2, I have tried various other ways trying to get it to work but couldn't work it out though I'm sure I've used something similar in the past with no issues.

(This is taking place in a loop if that changes anything, the error shows on the first run though so I don't think there is any issue with that portion.)

(Edited as I didn't realise the example I made confused people with my objective, my mistake, the first range was not meant to lead to the same place as the select at the bottom goes to.)

Toonee
  • 1
  • 2
  • This might be of help to you: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – eirikdaude Mar 11 '21 at 01:18
  • That all boils down to `Sheets("Sheet2").Range("B1").Offset(1,0).Select` – Tim Williams Mar 11 '21 at 01:23
  • @ eirikdaude Thank you, that seems like a very helpful page to look through, it's a lot of information though and I'm not quite currently getting it on my first couple of read throughs. – Toonee Mar 11 '21 at 01:36
  • @Timwilliams That helps me semi understand how to simplify / condense it so thank you, do you know why the initial range.select I was using doesn't work though as if I'm understanding this correctly I would still have to write the next part as `Sheets("Sheet2").Range(Prod + NVCurrRN).select` would I not? I assume that would still have the issue of it not accepting the 2 from NVCurrRN if it's still using the range option? – Toonee Mar 11 '21 at 01:49
  • In VBA the concatenation operator is `&`. While `+` might work most of the time, sometimes it doesn't. Eg `"B" + "2"` is fine, but `"B" + 2` will give you a Type Mismatch – Tim Williams Mar 11 '21 at 01:52
  • @timwilliams I see, thank you that solved it, I appreciate you taking time to help, now I think I need to concentrate on moving everything into single lines like you have done above. Thanks again! – Toonee Mar 11 '21 at 02:03

1 Answers1

-1

I'm dumb and used + instead of &, thanks to Timwilliams for the answer.

I think it's meant to be written like below.

Prod = "B"

Sheets("Sheet2").Range("A1").Offset(1,0).Select
NVCurrRN = ActiveCell.Row
Range(Prod & NVCurrRN).Select

(Edited as I didn't realise the example I made confused people with my objective, my mistake, the first range was not meant to lead to the same place as the select at the bottom goes to. It was just a quick example of the code before it to give the idea of what was happening.)

Toonee
  • 1
  • 2
  • Actually, following Tim's comment, it's meant to be written simply as one line: `Sheets("Sheet2").Range("B1").Offset(1,0).Select`. – BigBen Mar 11 '21 at 03:43