Help with variable
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 |
Help with variable
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 -- Regards, Tom Ogilvy "Steph" wrote in message ... 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 |
Help with variable
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("PODes cs"),2,False) strbody = strbody & cell.Value & " " & PODesc & vbNewLine Next -- HTH RP (remove nothere from the email address if mailing direct) "Steph" wrote in message ... 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 |
Help with variable
Thanks Tom!!
"Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Steph" wrote in message ... 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 |
All times are GMT +1. The time now is 06:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com