|
Posted to microsoft.public.excel.programming
|
|
|
VLookUP ARRAY , how can I make it not relative in macro?
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
|