VLookup and IF with VBA

[Jeff]

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]

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

[Jeff]

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]

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

[Jeff]

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]

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

[Jeff]

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]

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

[Jeff]

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]

if iserror(res) then
res = "Missing in table" 'or whatever you want to see
end if
worksheets("YouDidn'tSayWhatOne").range("F3").value = 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