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

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


--
crowdx42
------------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749
View this thread: http://www.excelforum.com/showthread...hreadid=573897

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


--
crowdx42
------------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749
View this thread: http://www.excelforum.com/showthread...hreadid=573897