VLookUP ARRAY , how can I make it not relative in macro?

[crowdx42]

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

 

[Mark]

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

You have to make the inserted formula into an absolute reference. A1
is relative whereas $A$1 is absolute. Figure out how to do that and
you should be golden.

 

[crowdx42]

Well I have tried changing the R[484]C[10], reference to the actual cell
reference and this gives me an error.
ie. I changed it to A2600 and I got an error on this.
Will have a look again.
Patrick

 

[Rich J]

Since your array is a fixed size, try giving the whole array a name and using
the array name in the code instead of cell references.

 

[Rich J]

Did you get it to work ?

Since your array is a fixed size, try giving the whole array a name and using
the array name in the code instead of cell references.

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

 

[crowdx42]

I ended up using the entire sheet for the array and this worked perfect
for me
I did try absolute using ({}) but this was returning an error for me.
Patrick