| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup VBA code
I am trying to use vlookup in a macro. I have to use
ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
Cells(ActiveCell.Row,"A")
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
Cells(ActiveCell.Row,1).SELECT
If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
thank Luke,
However I need more help if you could. For the second argument it is going to be an another workbook called "sourcedata". the range is from F1 to the furthers column to the right and the furthest row down. How would I write that? so far I have ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), soourcedata.xls.range(F1,..........), 4, False) Not even sure if any of the second argument is correct. thanks "Luke M" wrote: Cells(ActiveCell.Row,"A") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
Dim rngTemp As Range
Set rngTemp = Workbooks("soourcedata.xls"). _ Worksheets("Sheet1").Range("F1:I100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _ "A"), rngTemp, 4, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: thank Luke, However I need more help if you could. For the second argument it is going to be an another workbook called "sourcedata". the range is from F1 to the furthers column to the right and the furthest row down. How would I write that? so far I have ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), soourcedata.xls.range(F1,..........), 4, False) Not even sure if any of the second argument is correct. thanks "Luke M" wrote: Cells(ActiveCell.Row,"A") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
I am getting an error that says "type mismatch"
"Jacob Skaria" wrote: Dim rngTemp As Range Set rngTemp = Workbooks("soourcedata.xls"). _ Worksheets("Sheet1").Range("F1:I100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _ "A"), rngTemp, 4, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: thank Luke, However I need more help if you could. For the second argument it is going to be an another workbook called "sourcedata". the range is from F1 to the furthers column to the right and the furthest row down. How would I write that? so far I have ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), soourcedata.xls.range(F1,..........), 4, False) Not even sure if any of the second argument is correct. thanks "Luke M" wrote: Cells(ActiveCell.Row,"A") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
at first glance perhaps this line: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), rngTemp, 4, 0) should read: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell*.Row*, "A"), rngTemp, 4, 0) (as originally suggested) -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
Try the below test
Col A Col B Col C Col D One 11 12 13 Two 21 22 23 Three 31 32 33 Four 41 42 43 One XX In the above table place your cursor in xx (cell B5) and try the below code. It should vlookup the text mentioned in A5 in the array A1:D4 and return the corresponding value from 4th Column of the array Dim rngTemp As Range Set rngTemp = Worksheets("Sheet1").Range("A1:D4") ActiveCell.Value = WorksheetFunction.VLookup(Range("A" & _ ActiveCell.Row), rngTemp, 4, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: I am getting an error that says "type mismatch" "Jacob Skaria" wrote: Dim rngTemp As Range Set rngTemp = Workbooks("soourcedata.xls"). _ Worksheets("Sheet1").Range("F1:I100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _ "A"), rngTemp, 4, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: thank Luke, However I need more help if you could. For the second argument it is going to be an another workbook called "sourcedata". the range is from F1 to the furthers column to the right and the furthest row down. How would I write that? so far I have ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), soourcedata.xls.range(F1,..........), 4, False) Not even sure if any of the second argument is correct. thanks "Luke M" wrote: Cells(ActiveCell.Row,"A") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
Here is the code
Dim srccom As Range Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), srccom, 5, 0) Returns Run-time error'13': Type mismatch "cluckers" wrote: I am getting an error that says "type mismatch" "Jacob Skaria" wrote: Dim rngTemp As Range Set rngTemp = Workbooks("soourcedata.xls"). _ Worksheets("Sheet1").Range("F1:I100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _ "A"), rngTemp, 4, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: thank Luke, However I need more help if you could. For the second argument it is going to be an another workbook called "sourcedata". the range is from F1 to the furthers column to the right and the furthest row down. How would I write that? so far I have ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), soourcedata.xls.range(F1,..........), 4, False) Not even sure if any of the second argument is correct. thanks "Luke M" wrote: Cells(ActiveCell.Row,"A") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
After adding "row" it returns the error "unable to get the vlookup property
of the WorksheetFunction class". "p45cal" wrote: at first glance perhaps this line: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), rngTemp, 4, 0) should read: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell*.Row*, "A"), rngTemp, 4, 0) (as originally suggested) -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
Try the below
Dim srccom As Range Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100") ActiveCell.Value = WorksheetFunction.VLookup(Range("A" & _ ActiveCell.Row), srccom, 5, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: Here is the code Dim srccom As Range Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), srccom, 5, 0) Returns Run-time error'13': Type mismatch "cluckers" wrote: I am getting an error that says "type mismatch" "Jacob Skaria" wrote: Dim rngTemp As Range Set rngTemp = Workbooks("soourcedata.xls"). _ Worksheets("Sheet1").Range("F1:I100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _ "A"), rngTemp, 4, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: thank Luke, However I need more help if you could. For the second argument it is going to be an another workbook called "sourcedata". the range is from F1 to the furthers column to the right and the furthest row down. How would I write that? so far I have ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), soourcedata.xls.range(F1,..........), 4, False) Not even sure if any of the second argument is correct. thanks "Luke M" wrote: Cells(ActiveCell.Row,"A") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
Very likely because what it's looking for isn't to be found. I tried this: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) and it worked, but then gave the error you describe if what was sought didn't exist. Try the likes of this:Dim srccom As Range Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100") xxx = Empty On Error Resume Next xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) On Error GoTo 0 'cancels the On Error Resume Next If Not IsEmpty(xxx) Then ActiveCell.Value = xxx -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
The problem is that there is not going to be a vlookup value for all active
cells. When you run this is excel t returns a value of #N/A but in VBA it returns an error. Do you know how to get around the error so it keeps running the macro? "Jacob Skaria" wrote: Try the below Dim srccom As Range Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100") ActiveCell.Value = WorksheetFunction.VLookup(Range("A" & _ ActiveCell.Row), srccom, 5, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: Here is the code Dim srccom As Range Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), srccom, 5, 0) Returns Run-time error'13': Type mismatch "cluckers" wrote: I am getting an error that says "type mismatch" "Jacob Skaria" wrote: Dim rngTemp As Range Set rngTemp = Workbooks("soourcedata.xls"). _ Worksheets("Sheet1").Range("F1:I100") ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, _ "A"), rngTemp, 4, 0) If this post helps click Yes --------------- Jacob Skaria "cluckers" wrote: thank Luke, However I need more help if you could. For the second argument it is going to be an another workbook called "sourcedata". the range is from F1 to the furthers column to the right and the furthest row down. How would I write that? so far I have ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell, "A"), soourcedata.xls.range(F1,..........), 4, False) Not even sure if any of the second argument is correct. thanks "Luke M" wrote: Cells(ActiveCell.Row,"A") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: I am trying to use vlookup in a macro. I have to use ActiveCell.Value = WorksheetFunction.VLookup(........) because the active cell is in a column that will continuously move to the right. Therefore I can not use a static command. For the first argument of the vlookup how can I have it reference the cell is column A of the same row. If I can get that part I think I can get the rest. Thanks |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
Do I need to declare xxx as something?
"p45cal" wrote: Very likely because what it's looking for isn't to be found. I tried this: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) and it worked, but then gave the error you describe if what was sought didn't exist. Try the likes of this:Dim srccom As Range Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100") xxx = Empty On Error Resume Next xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) On Error GoTo 0 'cancels the On Error Resume Next If Not IsEmpty(xxx) Then ActiveCell.Value = xxx -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
No, its simply a variant to hold whatever the result of the vlookup is
(whether its an error, a number, a string, etc) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: Do I need to declare xxx as something? "p45cal" wrote: Very likely because what it's looking for isn't to be found. I tried this: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) and it worked, but then gave the error you describe if what was sought didn't exist. Try the likes of this:Dim srccom As Range Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100") xxx = Empty On Error Resume Next xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) On Error GoTo 0 'cancels the On Error Resume Next If Not IsEmpty(xxx) Then ActiveCell.Value = xxx -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
my code
Sub Macro3() ' Dim srccom As Range Set srccom = Workbooks("sourcedata.xls").Worksheets("Sheet1").R ange("F1:J100") xxx = Empty On Error Resume Next xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) On Error GoTo 0 If Not IsEmpty(xxx) Then ActiveCell.Value = xxx End Sub It says that xxx = Empty "Variable not defined" "Luke M" wrote: No, its simply a variant to hold whatever the result of the vlookup is (whether its an error, a number, a string, etc) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: Do I need to declare xxx as something? "p45cal" wrote: Very likely because what it's looking for isn't to be found. I tried this: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) and it worked, but then gave the error you describe if what was sought didn't exist. Try the likes of this:Dim srccom As Range Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100") xxx = Empty On Error Resume Next xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) On Error GoTo 0 'cancels the On Error Resume Next If Not IsEmpty(xxx) Then ActiveCell.Value = xxx -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup VBA code
I put in
Dim xxx as variant and it solved the problem Thanks for all the help "Luke M" wrote: No, its simply a variant to hold whatever the result of the vlookup is (whether its an error, a number, a string, etc) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "cluckers" wrote: Do I need to declare xxx as something? "p45cal" wrote: Very likely because what it's looking for isn't to be found. I tried this: ActiveCell.Value = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) and it worked, but then gave the error you describe if what was sought didn't exist. Try the likes of this:Dim srccom As Range Set srccom = ThisWorkbook.Worksheets("Sheet1").Range("F1:J100") xxx = Empty On Error Resume Next xxx = WorksheetFunction.VLookup(Cells(ActiveCell.Row, "A"), srccom, 5, 0) On Error GoTo 0 'cancels the On Error Resume Next If Not IsEmpty(xxx) Then ActiveCell.Value = xxx -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126806 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP as a vb code | Excel Discussion (Misc queries) | |||
| Using VLOOKUP in VBA code | Excel Worksheet Functions | |||
| VLOOKUP code needed please | Excel Worksheet Functions | |||
| VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
| how to use a VLOOKUP function in a VBA code? | New Users to Excel | |||
Linear Mode
