Help with variable

[Steph]

Hello. I use the variable strbody in the body of an e-mail. The variable
collects that data from the worksheet nicely. I was hoping to add a vlookup
formula to it.

So for example, the variable picks up the PO numbers in a given row. On the
same sheet (but in a hidden range) I have a table with all PO numbers and
their description. I would love the variable to concatenate the PO number
with the PO description, and set THAT as the variable.

Thank you for your help!

Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
strbody = strbody & cell.Value & vbNewLine
Next

 

[Tom Ogilvy]

Guessing that Cell contains the PO Numbers
Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
desc = application.Vlookup(cell.Value,Range("A1:C100"),2,0)
if iserror(desc) then
sStr1 = ""
else
sStr1 = ", " & desc
end if
strbody = strbody & cell.Value & sStr1 & vbNewLine
Next

 

[Bob Phillips]

Something like

Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
PODesc=Application.VLOOKUP(cell.Value,Range("PODescs"),2,False)
strbody = strbody & cell.Value & " " & PODesc & vbNewLine
Next

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Steph]

Thanks Tom!!

Guessing that Cell contains the PO Numbers
Set var = Range(Cells(ActiveCell.Row, "M"), _
Cells(ActiveCell.Row, "BJ").End(xlToLeft))
For Each cell In var
desc = application.Vlookup(cell.Value,Range("A1:C100"),2,0)
if iserror(desc) then
sStr1 = ""
else
sStr1 = ", " & desc
end if
strbody = strbody & cell.Value & sStr1 & vbNewLine
Next