| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying vlookup to a range
Hi,
I have to apply vlookup formula to a range which is nto pre-defined.The script must identify the last row in a column and than apply the formula after the last used row and 500 cells below that. What I have done is found then Last row using: LastRow = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _ searchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row I use the following formula for Vlookup for an identified range: Range("$B2:B1000").Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" I replace the above formula as below: Range("$B"&LastRow &:$1000).Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" This gives syntax error. It would be rally nice if someone can tell me how can I pass this variable to the Range.I tried various other options but it doesnot seem to work. Thanks a lot. Thanks and Regards, shal |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Applying vlookup to a range
Range("$B"&LastRow &":$B1000").Formula = _
HTH -- AP "Shal" a écrit dans le message de ... Hi, I have to apply vlookup formula to a range which is nto pre-defined.The script must identify the last row in a column and than apply the formula after the last used row and 500 cells below that. What I have done is found then Last row using: LastRow = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _ searchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row I use the following formula for Vlookup for an identified range: Range("$B2:B1000").Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" I replace the above formula as below: Range("$B"&LastRow &:$1000).Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" This gives syntax error. It would be rally nice if someone can tell me how can I pass this variable to the Range.I tried various other options but it doesnot seem to work. Thanks a lot. Thanks and Regards, shal |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Applying vlookup to a range
Thanks a lot for the syntax correction.One more thing.
When I apply to use the same syntax for Vlookup it gives a syntax error. Range("$B" & LastRow + 1 & ":$B1000").Formula = _ "=VLOOKUP("$A" & (LastRow +1) & ,'SAP DATA'!$A$12:$B$1000,2,FALSE)" It says invalid character for $A Thanks for the help. Thanks and Regards, Shalini Nahata "Ardus Petus" wrote: Range("$B"&LastRow &":$B1000").Formula = _ HTH -- AP "Shal" a écrit dans le message de ... Hi, I have to apply vlookup formula to a range which is nto pre-defined.The script must identify the last row in a column and than apply the formula after the last used row and 500 cells below that. What I have done is found then Last row using: LastRow = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _ searchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row I use the following formula for Vlookup for an identified range: Range("$B2:B1000").Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" I replace the above formula as below: Range("$B"&LastRow &:$1000).Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" This gives syntax error. It would be rally nice if someone can tell me how can I pass this variable to the Range.I tried various other options but it doesnot seem to work. Thanks a lot. Thanks and Regards, shal |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Applying vlookup to a range
Range("$B" & LastRow + 1 & ":$B1000").Formula = _
"=VLOOKUP($A" & Lastrow &" ,'SAP DATA'!$A$12:$B$1000,2,FALSE)" HTH -- AP "Shal" a écrit dans le message de ... Thanks a lot for the syntax correction.One more thing. When I apply to use the same syntax for Vlookup it gives a syntax error. Range("$B" & LastRow + 1 & ":$B1000").Formula = _ "=VLOOKUP("$A" & (LastRow +1) & ,'SAP DATA'!$A$12:$B$1000,2,FALSE)" It says invalid character for $A Thanks for the help. Thanks and Regards, Shalini Nahata "Ardus Petus" wrote: Range("$B"&LastRow &":$B1000").Formula = _ HTH -- AP "Shal" a écrit dans le message de ... Hi, I have to apply vlookup formula to a range which is nto pre-defined.The script must identify the last row in a column and than apply the formula after the last used row and 500 cells below that. What I have done is found then Last row using: LastRow = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _ searchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row I use the following formula for Vlookup for an identified range: Range("$B2:B1000").Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" I replace the above formula as below: Range("$B"&LastRow &:$1000).Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" This gives syntax error. It would be rally nice if someone can tell me how can I pass this variable to the Range.I tried various other options but it doesnot seem to work. Thanks a lot. Thanks and Regards, shal |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Applying vlookup to a range
Thanks Ardus. It was really helpful. Regards, Shals "Ardus Petus" wrote: Range("$B" & LastRow + 1 & ":$B1000").Formula = _ "=VLOOKUP($A" & Lastrow &" ,'SAP DATA'!$A$12:$B$1000,2,FALSE)" HTH -- AP "Shal" a écrit dans le message de ... Thanks a lot for the syntax correction.One more thing. When I apply to use the same syntax for Vlookup it gives a syntax error. Range("$B" & LastRow + 1 & ":$B1000").Formula = _ "=VLOOKUP("$A" & (LastRow +1) & ,'SAP DATA'!$A$12:$B$1000,2,FALSE)" It says invalid character for $A Thanks for the help. Thanks and Regards, Shalini Nahata "Ardus Petus" wrote: Range("$B"&LastRow &":$B1000").Formula = _ HTH -- AP "Shal" a écrit dans le message de ... Hi, I have to apply vlookup formula to a range which is nto pre-defined.The script must identify the last row in a column and than apply the formula after the last used row and 500 cells below that. What I have done is found then Last row using: LastRow = objExcel.Cells.Find(What:="*", After:=objExcel.Range("A1"), _ searchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row I use the following formula for Vlookup for an identified range: Range("$B2:B1000").Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" I replace the above formula as below: Range("$B"&LastRow &:$1000).Formula = _ "=VLOOKUP($A2,'SAP DATA'!$A$12:$B$1000,2,FALSE)" This gives syntax error. It would be rally nice if someone can tell me how can I pass this variable to the Range.I tried various other options but it doesnot seem to work. Thanks a lot. Thanks and Regards, shal thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Applying Formulas to a Range in a Worksheet | Excel Discussion (Misc queries) | |||
| Applying Conditional Formatting to a Max number in range | Excel Worksheet Functions | |||
| Applying Offset to Range in VBA | Excel Worksheet Functions | |||
| Applying data range to column width | Charts and Charting in Excel | |||
| Applying formula to only NON-EMPTY cells in range | Excel Discussion (Misc queries) | |||
Hybrid Mode
