VLookup and IF with VBA

[Guest]

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â€

 

[Dave Peterson]

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.)

 

[Guest]

Thank you.
The only thingis that "JC" is in the VBPA worksheet.

--
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.)

 

[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

Thank you.
The only thingis that "JC" is in the VBPA worksheet.

 

[Guest]

Hi,
(which sheet owns E3) billable jobs
E3 is in billable jobs
--
Regards,
Jeff

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

 

[Dave Peterson]

Then use the first.

Hi,
(which sheet owns E3) billable jobs
E3 is in billable jobs

 

[Guest]

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?

 

[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...

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?

 

[Guest]

Thank you it worked
how can I put the value of "res" in row F3?

 

[Dave Peterson]

if iserror(res) then
res = "Missing in table" 'or whatever you want to see
end if
worksheets("YouDidn'tSayWhatOne").range("F3").value = res

Thank you it worked
how can I put the value of "res" in row F3?