VBA Code to Lookup value in range & update cell in row

[Patchworks]

I have a function that is called upon saving a UserForm. One of the Text Labels contains the ID field that I need to use to do a lookup into the first column of a Named Range and then I need to update the cell in the 4th column of that row.

From my research, I'm assuming it will go something like this:

Application.WorksheetFunction.VLookup(valve, Worksheets("TestWorksheet").Range("TestRange".Cells), 10, False).Value = "TestValue"

Can someone help me out?

 

[Patchworks]

Actually for Match and Vlookup you should change it to

APPLICATION.match and APPLICATION.vlookup.

you're probably getting the error because the match/vlookup failed to find the key. If "Forums" is a named range, you don't need the Quotes around it..

Cells(WorksheetFunction.Match(frmForumsPosting.ForumID.Caption, Forums, 0), 10) = "On Hold"

You'll probably want to evaluate the expression prior to executing it to determine if it's going to be valid, unless you're confident a match will always be found..

eg Iserror(expression)

phxsportz,

Yes, "Forums" is a named range! Unfortunately, I'm not very good with VBA. Would you mind showing me an example of the code your are referring to?

Thanks so much for you patience and help.

g

 

[phxsportz]

Ok.. what is the Range that is assigned to Forums ? It must be a column or a row, but it can't be multidimensional for use with MATCH(). It will always return "N/A#" if it's multidimensional eg (A1:B10)

Cells(application.Match(frmForumsPosting.ForumID.Caption, Forums, 0), 10) = "On Hold"