|
VLookup and IF with VBA
I need to design a VBA formula for the following:
In worksheet Billable Jobs, I want to do Vlookup of the value of row E3 in table_Array = worksheet VBPA in Col_index_num Column D if and only if in the column C there the text JC -- Regards, Jeff |
Maybe...
Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet Billable Jobs, I want to do Vlookup of the value of row E3 in table_Array = worksheet VBPA in Col_index_num Column D if and only if in the column C there the text JC -- Regards, Jeff -- Dave Peterson |
Thank you.
The only thingis that "JC" is in the VBPA worksheet. -- Regards, Jeff "Dave Peterson" wrote: Maybe... Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet âœBillable Jobsâ, I want to do Vlookup of the value of row âœE3â in table_Array = worksheet âœVBPAâ in Col_index_num Column âœDâ if and only if in the column âœCâ there the text âœJCâ -- Regards, Jeff -- Dave Peterson |
I'm not sure what should be looked up (which sheet owns E3)??
maybe... with worksheets("billable jobs") if ucase(worksheets("vbpa").range("C3").value) = "JC" then Or maybe... with worksheets("VBPA") if ucase(.range("C3").value) = "JC" then Jeff wrote: Thank you. The only thingis that "JC" is in the VBPA worksheet. -- Regards, Jeff "Dave Peterson" wrote: Maybe... Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet âœBillable Jobsâ, I want to do Vlookup of the value of row âœE3â in table_Array = worksheet âœVBPAâ in Col_index_num Column âœDâ if and only if in the column âœCâ there the text âœJCâ -- Regards, Jeff -- Dave Peterson -- Dave Peterson |
Hi,
(which sheet owns E3) billable jobs E3 is in billable jobs -- Regards, Jeff "Dave Peterson" wrote: I'm not sure what should be looked up (which sheet owns E3)?? maybe... with worksheets("billable jobs") if ucase(worksheets("vbpa").range("C3").value) = "JC" then Or maybe... with worksheets("VBPA") if ucase(.range("C3").value) = "JC" then Jeff wrote: Thank you. The only thingis that "JC" is in the VBPA worksheet. -- Regards, Jeff "Dave Peterson" wrote: Maybe... Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet ââ¬ÅBillable Jobsââ¬Â, I want to do Vlookup of the value of row ââ¬ÅE3ââ¬Â in table_Array = worksheet ââ¬ÅVBPAââ¬Â in Col_index_num Column ââ¬ÅDââ¬Â if and only if in the column ââ¬ÅCââ¬Â there the text ââ¬ÅJCââ¬Â -- Regards, Jeff -- Dave Peterson -- Dave Peterson |
Then use the first.
Jeff wrote: Hi, (which sheet owns E3) billable jobs E3 is in billable jobs -- Regards, Jeff "Dave Peterson" wrote: I'm not sure what should be looked up (which sheet owns E3)?? maybe... with worksheets("billable jobs") if ucase(worksheets("vbpa").range("C3").value) = "JC" then Or maybe... with worksheets("VBPA") if ucase(.range("C3").value) = "JC" then Jeff wrote: Thank you. The only thingis that "JC" is in the VBPA worksheet. -- Regards, Jeff "Dave Peterson" wrote: Maybe... Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet ââ¬ÅBillable Jobsââ¬Â, I want to do Vlookup of the value of row ââ¬ÅE3ââ¬Â in table_Array = worksheet ââ¬ÅVBPAââ¬Â in Col_index_num Column ââ¬ÅDââ¬Â if and only if in the column ââ¬ÅCââ¬Â there the text ââ¬ÅJCââ¬Â -- Regards, Jeff -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Thank you.
Would you know if there's way to make it more dynamic. If I wanted to evaluate the column E in Billable Job. to column C in VBPA? -- Regards, Jeff "Dave Peterson" wrote: Then use the first. Jeff wrote: Hi, (which sheet owns E3) billable jobs E3 is in billable jobs -- Regards, Jeff "Dave Peterson" wrote: I'm not sure what should be looked up (which sheet owns E3)?? maybe... with worksheets("billable jobs") if ucase(worksheets("vbpa").range("C3").value) = "JC" then Or maybe... with worksheets("VBPA") if ucase(.range("C3").value) = "JC" then Jeff wrote: Thank you. The only thingis that "JC" is in the VBPA worksheet. -- Regards, Jeff "Dave Peterson" wrote: Maybe... Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet âââšÂ¬ÃâœBillable JobsâââšÂ¬ÃÂ, I want to do Vlookup of the value of row âââšÂ¬ÃâœE3â⠚¬àin table_Array = worksheet âââšÂ¬ÃâœVBPAâà âšÂ¬Ã in Col_index_num Column âââšÂ¬ÃâœDââ⠚¬Ã if and only if in the column âââšÂ¬ÃâœCââ⠚¬Ã there the text âââšÂ¬ÃâœJCâ⠚¬à-- Regards, Jeff -- Dave Peterson -- Dave Peterson -- Dave Peterson |
I'm not sure how the dynamic portion comes in, but if you qualify the ranges,
you'll be ok. But if you have rules, you could use something like: Dim CellToCheckForJC as range dim CellWithValueToMatch as range if something = true then set celltocheckforjc = worksheets("sheet99").range("d43") else set celltocheckforjc = worksheets("sheet001").range("F99") end if if somethingelse = true then set CellWithValueToMatch = worksheets("sheet108").range("a1") else set CellWithValueToMatch = worksheets("sheet2").range("b99") end if 'then just use those ranges: if ucase(celltocheckforjc.value) = "JC" then res = application.vlookup(cellwithvaluetomatch.value, _ lookuprng,col_index_num,0) ''''and so on... Jeff wrote: Thank you. Would you know if there's way to make it more dynamic. If I wanted to evaluate the column E in Billable Job. to column C in VBPA? -- Regards, Jeff "Dave Peterson" wrote: Then use the first. Jeff wrote: Hi, (which sheet owns E3) billable jobs E3 is in billable jobs -- Regards, Jeff "Dave Peterson" wrote: I'm not sure what should be looked up (which sheet owns E3)?? maybe... with worksheets("billable jobs") if ucase(worksheets("vbpa").range("C3").value) = "JC" then Or maybe... with worksheets("VBPA") if ucase(.range("C3").value) = "JC" then Jeff wrote: Thank you. The only thingis that "JC" is in the VBPA worksheet. -- Regards, Jeff "Dave Peterson" wrote: Maybe... Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet âââšÂ¬ÃâœBillable JobsâââšÂ¬ÃÂ, I want to do Vlookup of the value of row âââšÂ¬ÃâœE3â⠚¬àin table_Array = worksheet âââšÂ¬ÃâœVBPAâà âšÂ¬Ã in Col_index_num Column âââšÂ¬ÃâœDââ⠚¬Ã if and only if in the column âââšÂ¬ÃâœCââ⠚¬Ã there the text âââšÂ¬ÃâœJCâ⠚¬à-- Regards, Jeff -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Thank you it worked
how can I put the value of "res" in row F3? -- Regards, Jeff "Dave Peterson" wrote: I'm not sure how the dynamic portion comes in, but if you qualify the ranges, you'll be ok. But if you have rules, you could use something like: Dim CellToCheckForJC as range dim CellWithValueToMatch as range if something = true then set celltocheckforjc = worksheets("sheet99").range("d43") else set celltocheckforjc = worksheets("sheet001").range("F99") end if if somethingelse = true then set CellWithValueToMatch = worksheets("sheet108").range("a1") else set CellWithValueToMatch = worksheets("sheet2").range("b99") end if 'then just use those ranges: if ucase(celltocheckforjc.value) = "JC" then res = application.vlookup(cellwithvaluetomatch.value, _ lookuprng,col_index_num,0) ''''and so on... Jeff wrote: Thank you. Would you know if there's way to make it more dynamic. If I wanted to evaluate the column E in Billable Job. to column C in VBPA? -- Regards, Jeff "Dave Peterson" wrote: Then use the first. Jeff wrote: Hi, (which sheet owns E3) billable jobs E3 is in billable jobs -- Regards, Jeff "Dave Peterson" wrote: I'm not sure what should be looked up (which sheet owns E3)?? maybe... with worksheets("billable jobs") if ucase(worksheets("vbpa").range("C3").value) = "JC" then Or maybe... with worksheets("VBPA") if ucase(.range("C3").value) = "JC" then Jeff wrote: Thank you. The only thingis that "JC" is in the VBPA worksheet. -- Regards, Jeff "Dave Peterson" wrote: Maybe... Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅBillable JobsÃÆââââ¬Å¡Ã ¬ÃâšÃÂ, I want to do Vlookup of the value of row ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅE3ÃÆÃ¢à ¢ââ¬Å¡Ã¬ÃâšÃ in table_Array = worksheet ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅVBPAÃÆ⠃¢ââ¬Å¡Ã¬ÃâšÃ in Col_index_num Column ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅDÃÆâà ¢ââ¬Å¡Ã¬ÃâšÃ if and only if in the column ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅCÃÆâà ¢ââ¬Å¡Ã¬ÃâšÃ there the text ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅJCÃÆÃ¢à ¢ââ¬Å¡Ã¬ÃâšÃ -- Regards, Jeff -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
if iserror(res) then
res = "Missing in table" 'or whatever you want to see end if worksheets("YouDidn'tSayWhatOne").range("F3").valu e = res Jeff wrote: Thank you it worked how can I put the value of "res" in row F3? -- Regards, Jeff "Dave Peterson" wrote: I'm not sure how the dynamic portion comes in, but if you qualify the ranges, you'll be ok. But if you have rules, you could use something like: Dim CellToCheckForJC as range dim CellWithValueToMatch as range if something = true then set celltocheckforjc = worksheets("sheet99").range("d43") else set celltocheckforjc = worksheets("sheet001").range("F99") end if if somethingelse = true then set CellWithValueToMatch = worksheets("sheet108").range("a1") else set CellWithValueToMatch = worksheets("sheet2").range("b99") end if 'then just use those ranges: if ucase(celltocheckforjc.value) = "JC" then res = application.vlookup(cellwithvaluetomatch.value, _ lookuprng,col_index_num,0) ''''and so on... Jeff wrote: Thank you. Would you know if there's way to make it more dynamic. If I wanted to evaluate the column E in Billable Job. to column C in VBPA? -- Regards, Jeff "Dave Peterson" wrote: Then use the first. Jeff wrote: Hi, (which sheet owns E3) billable jobs E3 is in billable jobs -- Regards, Jeff "Dave Peterson" wrote: I'm not sure what should be looked up (which sheet owns E3)?? maybe... with worksheets("billable jobs") if ucase(worksheets("vbpa").range("C3").value) = "JC" then Or maybe... with worksheets("VBPA") if ucase(.range("C3").value) = "JC" then Jeff wrote: Thank you. The only thingis that "JC" is in the VBPA worksheet. -- Regards, Jeff "Dave Peterson" wrote: Maybe... Option explicit sub testme() dim lookupRng as range dim res as variant dim col_index_num as long 'include more columns if necessary set lookuprng = worksheets("VBPA").range("a:Z") col_index_num = 13 'what should it be? with worksheets("billable jobs") if ucase(.range("C3").value) = "JC" then res = application.vlookup(.range("E3").value, lookuprng,col_index_num,0) if iserror(res) then 'there was no match else msgbox "returned: " & res end if end if end with end sub (If I understood your references correctly.) Jeff wrote: I need to design a VBA formula for the following: In worksheet ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅBillable JobsÃÆââââ¬Å¡Ã ¬ÃâšÃÂ, I want to do Vlookup of the value of row ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅE3ÃÆÃ¢à ¢ââ¬Å¡Ã¬ÃâšÃ in table_Array = worksheet ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅVBPAÃÆ⠃¢ââ¬Å¡Ã¬ÃâšÃ in Col_index_num Column ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅDÃÆâà ¢ââ¬Å¡Ã¬ÃâšÃ if and only if in the column ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅCÃÆâà ¢ââ¬Å¡Ã¬ÃâšÃ there the text ÃÆââââ¬Å¡Ã¬ Ãâ¦Ã¢â¬ÅJCÃÆÃ¢à ¢ââ¬Å¡Ã¬ÃâšÃ -- Regards, Jeff -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 02:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com