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

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 wrote:
> 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.

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 A2:P600 and I got an error on this.
Will have a look again.
Patrick

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.

"crowdx42" wrote:

>
> 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
> ------------------------------------------------------------------------
> crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749
> View this thread: http://www.excelforum.com/showthread...hreadid=573897
>
>

Did you get it to work ?

"Rich J" wrote:

> 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.
>
> "crowdx42" wrote:
>
> >
> > 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
> > ------------------------------------------------------------------------
> > crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749
> > View this thread: http://www.excelforum.com/showthread...hreadid=573897
> >
> >

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