Hi,
so all is great with ONE exception, when I insert VLookUP with a macro, the array is inserting relative to the insert cell. How can I make it refer to a fixed array? Below is the code I have. The code is searching for a word and then inserting the Vlookup in the cell to the right of the word.
Thanks
Patrick


Sub Insert_VLOOKUP_FULL()
Dim Findfirst As Object, FindNext As Object, FindNext2 As Object
Set Findfirst = Cells.Find(What:="ATM CARDS", LookIn:=xlValues)
If Not Findfirst Is Nothing Then
Findfirst.Select
With Range("A" & Findfirst.Row & ":F" & Findfirst.Row).Borders(xlEdgeTop)
ActiveCell.Offset(, 4) = "=VLOOKUP(RC[-5],'Product per Call'!R[-2]C[-5]:R[484]C[10],16,FALSE)"
End With
Set FindNext2 = Findfirst
Do
Set FindNext = Cells.FindNext(After:=FindNext2)
If Not FindNext Is Nothing Then
With Range("A" & FindNext.Row & ":F" & FindNext.Row).Borders(xlEdgeTop)
ActiveCell.Offset(, 4) = "=VLOOKUP(RC[-5],'Product per Call'!R[-2]C[-5]:R[484]C[10],16,FALSE)"
End With
End If
Set FindNext2 = FindNext
FindNext2.Interior.ColorIndex = 0
FindNext2.Select
Loop Until FindNext.Address = Findfirst.Address