vlookup in vba

[ROB IN RWC]

I wish to find a value in a worksheet table, which I can do as a function within the worksheet by using the VLookup function. I am trying to automate the calculation of this by placing it as part of a macro, but I can not find the syntax in the online help.


Can someone give me a sample code that shows how you use a worksheet
function (VLookup) within a VBA script, capture the result and then set the
value of a cell?

I know this doesn't work, but this is what I am looking for...

=vlookup(activecell.value,named_range,2,false)

Thank you!

[RobDog888]

The .Find method should be easier for you if you want vba code. Otherwise you can record a macro of your desired actions and stop. Then check out the geneated code in the module section.

[ROB IN RWC]

if my worksheet has

thu 1
fri 2
sat 3
in cells a1:b3

what would the code (within the macro) look like to be able to lookup friday and return the value 2.

something like...

varDate = codecodecode

Then I could use varDate as another variable.

This is all part of a email script where the day is used as part of the .body of the email.

.Body = "Hello " & varFirs & "," & vbCrLf & "Your service commitment is on " & varDate & ". That's " & varDay & " between " & varTime2 & " " & varTime3 & " " & varTime4 & "." & vbCrLf & "You will be filling the position of " & varPosi & "." & vbCrLf & "Please respond to this email as soon as possible to confirm that you will be able to attend and fulfill your commitment." & vbCrLf & vbCrLf & myform.txtBody.Text & vbCrLf & vbCrLf & "Thank You," & vbCrLf & "Jennie C

On a side note, I am not able to use "space_" to drop this large script on more than one line. It turns it all red.

thanks for your help.

[RobDog888]

Did you record a macro of you creating the vlookup for this yet?

To do a space_ line continuation yoou need to close any double quotes on your string and inclide an ampersand character.

"This is a long text of code" _
& " and it continues on the next line of code."

[ROB IN RWC]

Yes I did create a macro, but I am to select one lookup variable, i.e. A1 (or the named range for A1).

The macro I need it for tests various criteria based on ActiveCell.Value and ActiveCell.Offset. I need the lookup function to use one of these ActiveCell objects as the lookup value. Because of this, it seems to me that it needs to be imbedded in the code of the particular subroutine.

Does this make sense?

I understand the & deal, thanks.

[RobDog888]

Sounds like you may be needing more then what vloopup can do for you.

Did you look at the .Find method as I think it will be more of what you want to use.

[ROB IN RWC]

I'll check it out.
Thanks.