Thread: Change CURRENT CELL

After using the FIND command to locate a cell in a particular column, it is my understanding that the 'found' cell becomes the CURRENT CELL. How do I now cause the cell that is perhaps 4 columns to the left and on the same row as the found cell to become the CURRENT CELL?
I've struggled....

Hi bobptex and welcome to the forum,

VBA has lots of tools to deal with ranges.

It will understand

ActiveCell.Row and ActiveCell.Column and these will come back as numbers.
Before you do your Find you could save the possition of your activecell, something like

myrow = ActiveCell.Row
mycol = ActiveCell.Column

Then do your find and to get back do a

Cells(myrow,mycol).select

Does the above help?

You may also find the OFFSET command of use.

Cells(myrow,mycol).offset(0,-4).Select

or

Selection.offset(0,-4).Select

would select the cell 4 to the left of the current cell.

If you have used a FIND command, then just tag .OFFSET(0, -4) on the end (prior to any select or other action) and you will be referring to the cell 4 to the left:

Cells.Find(What:="Bob", LookIn:=xlFormulas).Offset(0, -4).Activate

Note - if the above doesn't find "Bob" you will get an error. You may need error trapping.

Cheers, Rob.