Trying to select a cell with a variable as a column number

Question

I'm trying to set up a code that will find a cell using the find function and then select a different cell in that column. I store the column number as a variable, and then try to move to a cell in the same column using the variable, but its not working.

I've tried changing it so that the column is stored as a string instead of an integer and tried using the .Cells method instead, neither have worked.

Dim numCol As String

Cells.Find(what:="e", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= 
_
    False, SearchFormat:=False).Activate

numCol = ActiveCell.Column

Range(numCol & "4").Select

Getting a 1004 "method range of object global failed" error from that last line of code.

`Dim numCol as Long`, then `Cells(4, numCol)`. But you probably don't need to use `Activate` or `Select`. See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jun 19 '19 at 21:36
`numCol` is an integer. casting it to string will give you something like `Range("14").Select`, and that's not a valid range identifier. BigBen has the solution, this is just mis-using or misunderstanding how `Range` arguments work. Cheers. — David Zemens, Jun 19 '19 at 21:38

pgSystemTester · Answer 1 · 2019-06-20T00:42:15.173

Here's some modified code from your query that might be useful. You want to try to use the properties of the range. In the below example I've defined a range that is the found cell fcell. A couple examples of what you can do ar there.

Dim numCol As Long
Dim fcell As Range

'this will find the cell and set it as a variable of fcell
Set fcell = Cells.Find(what:="e", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

'you can select it
fcell.Select

'you can select the whole column
fcell.EntireColumn.Select

 'you can select the column number (if one column)
 numCol = fcell.Column

'you can return the address
MsgBox "the address is " & fcell.Address

Good luck.

Trying to select a cell with a variable as a column number

1 Answers1