(Solved) – VBA Code for current cell works for cell underneath when pressing enter, not current cell


First of all, I’ve not heard of VBA before yesterday, but not a total noob in other languages (Python, Java). Put this piece of code together with help from the internet. The idea is that when you enter a string in a cell in worksheet “B” (in range B1:B200), it finds that string in worksheet “A” (in column A), takes the value that is next to it on the right side, takes that value and prints in next to the string in worksheet “B”.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("B1:B200")) Is Nothing Then

Dim xlRange As Range
Dim xlCell As Range
Dim xlSheet As Worksheet
Dim valueToFind

valueToFind = Target.Value
Set xlSheet = ActiveWorkbook.Worksheets("A")
Set xlRange = xlSheet.Range("A:A")

For Each xlCell In xlRange
    If IsEmpty(xlCell) = True Then
    MsgBox("Not found!")    
    Exit For
        End If
    If xlCell.Value = valueToFind Then
        Target.Offset(0, 1) = xlCell.Offset(0, 1)
        MsgBox ("Found!")
        Exit For
        End If

Next xlCell

End If
End Sub

It almost works, except from the part that when you input a value in cell B1 and you press enter, the cursor goes to B2 and apllies the code there. So when I input a string in B1 that is in worksheet “A”, it gives me “Not found!”, because B2 is empty.

Is there an easy fix for this?

Have a nice day

Leave a Reply

Your email address will not be published. Required fields are marked *