Help with Sub

[claudehollett]

I'm having trouble with the following Sub. Can someone tell me what is wrong?

I'm trying to select a single cell based on the FinalRow variable as follows:

Sub test()
FinalRow = Range("A65536").End(xlUp).Row
Range(Cells(FinalRow - 2, 34)).Select

End Sub

Thanks for any help.

 

[Von Pookie]

You want to use either Range or Cells--not both.

The Cells property will accept either the column number as you are using, or the column letter (entered with quotes, e.g. "AH"):
Cells(FinalRow - 2, 34).Select
Cells(FinalRow - 2, "AH").Select

The Range property needs the column letters and the & symbol, as you are essentially building a reference such as "AH3". The & symbol connects the two parts :
Range("AH" & FinalRow -2).Select

 

[pgc01]

Hi claudehollett

You can also select the cell directly

Sub test()
Range("A65536").End(xlUp).Offset(-2, 33).Select

End Sub

Hope this helps
PGC

 

[Von Pookie]

It should be pointed out, however that depending on what you are wanting to do it is highly possible that you needn't select the cell(s) in the first place. You can usually interact with them directly. For example, if you color a cell yellow, the macro recorder will give you this:

Range("A1").Select
Selection.Interior.ColorIndex = 6

This will do the exact same thing, just without needing to select the cell:

Range("A1").Interior.ColorIndex = 6