Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLOOKUP in VBA
Just need a quick clarification; I am trying to find a value (Job
Number) in the same row as the DueDate variable shown in the procedure below. I can't get it to work, I was assuming it would find the closest date if there were no matches. Is this correct? dim rngdate as range dim WS as worksheet rngDate = WS.Application.WorksheetFunction.VLookup(DueDate, "G1:G500", 5, True) I would like to search through column G for the DueDate, and return the corresponding Job Number in column 5. Thanks for any help, Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLOOKUP in VBA
dim rngdate as range
dim WS as worksheet Dim DueDate as String Dim FoundRng AsRange Set WS = Worksheets("Sheet1") 'or whatever sheet Set rngDate = WS.Range("G1:G500") FoundRng = Application..VLookup(DueDate, rngDate,5, True) It is always better to set your ranges to a variable outside of Worksheet Functions and use the varible within the function. This is untested, so give it a try. Notice I declared your DueDate as a String for VLookup to use. Mike F wrote in message oups.com... Just need a quick clarification; I am trying to find a value (Job Number) in the same row as the DueDate variable shown in the procedure below. I can't get it to work, I was assuming it would find the closest date if there were no matches. Is this correct? dim rngdate as range dim WS as worksheet rngDate = WS.Application.WorksheetFunction.VLookup(DueDate, "G1:G500", 5, True) I would like to search through column G for the DueDate, and return the corresponding Job Number in column 5. Thanks for any help, Chad |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLOOKUP in VBA
Hi Chad:
Vlookup needs the key to be on the left of the data. As is: 'assume that DueDate is already a valid value dim WS as worksheet dim v as variant set WS =activesheet v = Application.WorksheetFunction.VLookup( _ DueDate, WS.range("G1:K500"), 5, True) In your case: dim WS as worksheet dim v as variant set WS =activesheet v = Application.WorksheetFunction.Match( _ DueDate, WS.range("G1:G500"), 1) if isna(v) then msgbox "cannot match",vbokonly,"error" exit sub end if v=WS.cells(v, 5) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. " wrote: Just need a quick clarification; I am trying to find a value (Job Number) in the same row as the DueDate variable shown in the procedure below. I can't get it to work, I was assuming it would find the closest date if there were no matches. Is this correct? dim rngdate as range dim WS as worksheet rngDate = WS.Application.WorksheetFunction.VLookup(DueDate, "G1:G500", 5, True) I would like to search through column G for the DueDate, and return the corresponding Job Number in column 5. Thanks for any help, Chad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLOOKUP in VBA
Good catch!! I didn't even notice trying to return the 5th column of a one
column range. Mike F "Martin Fishlock" wrote in message ... Hi Chad: Vlookup needs the key to be on the left of the data. As is: 'assume that DueDate is already a valid value dim WS as worksheet dim v as variant set WS =activesheet v = Application.WorksheetFunction.VLookup( _ DueDate, WS.range("G1:K500"), 5, True) In your case: dim WS as worksheet dim v as variant set WS =activesheet v = Application.WorksheetFunction.Match( _ DueDate, WS.range("G1:G500"), 1) if isna(v) then msgbox "cannot match",vbokonly,"error" exit sub end if v=WS.cells(v, 5) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. " wrote: Just need a quick clarification; I am trying to find a value (Job Number) in the same row as the DueDate variable shown in the procedure below. I can't get it to work, I was assuming it would find the closest date if there were no matches. Is this correct? dim rngdate as range dim WS as worksheet rngDate = WS.Application.WorksheetFunction.VLookup(DueDate, "G1:G500", 5, True) I would like to search through column G for the DueDate, and return the corresponding Job Number in column 5. Thanks for any help, Chad |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VLOOKUP in VBA
On Jan 29, 7:33 pm, "Mike Fogleman" wrote: Good catch!! I didn't even notice trying to return the 5th column of a one column range. Mike F"Martin Fishlock" wrote in ... Hi Chad: Vlookup needs the key to be on the left of the data. Thanks for the assistance, unfortunately I have not remembered the golden rule of vlookup, always needs to be from the left. In this worksheet all of the values that need to be searched through have to be in the right most column. I was able to test both of your suggestions and they work very well with the correct ranges. They will always be useful for future projects. Thanks again, Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |