Originally Posted by
paulma1960
I'm looking for some code that, given the lookup value (in this case AB02, which is on cell Sheet1!B4), would insert in cells Sheet2!B11:B14, the formula =Sheet1!B$4.
Simple exsmple
Sub test()
Dim x, myStr
myStr = "AB02"
With Sheets("sheet1").ListObjects("table1")
x = Application.Match(myStr, .ListColumns(1).Range, 0)
If IsNumeric(x) Then
x = .Range(x, 1).Address(, , , 1)
Else
MsgBox "No match found": Exit Sub
End If
End With
Sheets("sheet2").[b11:b14].Formula = "=" & x
End Sub
Bookmarks