hello
I try making macro by match B2 into sheet2 with column A for sheet1 and brings the values into sheet2 for cells B3:B5. every thing is ok except one thing . if the cell B2 is empty should clear B3:B5 ,but it doesn't clear .
any help to fix it please?
thanks
I try making macro by match B2 into sheet2 with column A for sheet1 and brings the values into sheet2 for cells B3:B5. every thing is ok except one thing . if the cell B2 is empty should clear B3:B5 ,but it doesn't clear .
any help to fix it please?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Fnd As Range, sh1 As Worksheet
Set sh1 = sheet1
On Error Resume Next
If Target.Address(0, 0) = "B2" And Target.Value <> "" Then
Set Fnd = sheet1.Range("A:A").Find(Target.Value, , , xlWhole, , , False, , False)
If Not Fnd Is Nothing Then
With sheet2
.Range("B3").Value = Fnd.Offset(, 1).Value
.Range("B4").Value = Fnd.Offset(, 3).Value
.Range("B5").Value = Fnd.Offset(, 2).Value
End With
Else
sheet2.Range("B3").Value = ""
sheet2.Range("B4").Value = ""
sheet2.Range("B5").Value = ""
End If
End If
End Sub