lookup and select

[JoboM]

Does anyone know how to code vba to find a cell that has the same contents as a reference cell, say A1, and select that cell but offset by 1, ie. in the column to the left but same row??

 

[sericom]

Set rCell = Range("C2:C50").Find(Range("A1").Value, LookIn:=xlValues)
rCell.Offset(0, -1).Select

That will find the first occurance of A1 in the range C2:C50, and select the cell to the left of the match.

 

[JoboM]

Thanks! Would this code go in the module section? Otherwise how exactly would you input the code?? Sorry, I am quite new to macros

 

[sericom]

Depends when you want it to run. You could have a button that you click, or an on change event when A1 changes.

 

[sericom]

This will run whenever A1 changes

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Set rcell = Range("C2:C50").Find(Range("A1").Value, LookIn:=xlValues)
        If Not rcell Is Nothing Then
            rcell.Offset(0, -1).Select
        Else
            MsgBox "No match found"
        End If
    End If
End Sub

Right click your sheet tab, select 'View Code', then paste the code in. Go back to worksheet and change A1 to test.

 

[JoboM]

Thank you! This works brilliantly.

Would it be possible to also code a command that returns to A1 after information is put into the cell that was previously selected??

 

[sericom]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Set rcell = Range("C2:C50").Find(Range("A1").Value, LookIn:=xlValues)
        If Not rcell Is Nothing Then
            rcell.Offset(0, -1).Select
        Else
            MsgBox "No match found"
        End If
    End If
    If Not Intersect(Range("B2:B50"), Target) Is Nothing Then
        Range("A1").Select
    End If
End Sub