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
|