Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=573897 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookUP ARRAY , how can I make it not relative in macro?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookUP ARRAY , how can I make it not relative in macro?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookUP ARRAY , how can I make it not relative in macro?
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 |
#5
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookUP ARRAY , how can I make it not relative in macro?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup -Want to make table of array optional | Excel Discussion (Misc queries) | |||
How do i make a macro "relative" to cell? | Excel Discussion (Misc queries) | |||
Make macro-button sheet reference relative | Excel Programming | |||
Relative Position in VBA Array | Excel Programming | |||
Cannot make macro relative | Excel Programming |