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

P

Patchworks

New Member
Joined
Jul 15, 2009
Messages
23
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:

Code: 
Application.WorksheetFunction.VLookup(valve, Worksheets("TestWorksheet").Range("TestRange".Cells), 10, False).Value = "TestValue"
Can someone help me out?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Can you explain what you are trying to do in WORDS with an example?

Your code doesn't meake sense.
 
Upvote 0
You would use Match and Offset, not Vlookup.

Match will return the row you want to edit
ie ID = 01 Range = A1:A4 Match of "01" returns row 3


Code: 
cells(match(id,Range("A1:A4"),0),4) = "Whatever you want to put in the 4th cell of your Row"
 
Upvote 0
Seiya said:
Can you explain what you are trying to do in WORDS with an example?

Your code doesn't meake sense.
Click to expand...

Ok, I'm going to try best I can... I'm an old programmer from way back so I understand the concepts, but VBA specific syntax I don't.

Anyway, I have a spreadsheet and you click on a command button and it launches a UserForm for data entry. When it launches, I pass to the form a cell which is a Key Field used to do a lookup into the range so I can update the row I just launched from.

When you submit the form, I want to Use that Key Field to update a column in the row I launched the User Form from!

I'm struggling along but here is the code that I use to launch the User Form from:

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column > 1 Then Exit Sub
        If Target.Offset(0, 3) = "Yes" Then
            frmForumsPosting.ForumID = Target.Offset(0, 1).Value
            frmForumsPosting.ForumURL = Target.Offset(0, 2).Value
            frmForumsPosting.UserName = Target.Offset(0, 8).Value
            frmForumsPosting.Password = Target.Offset(0, 9).Value
        
            frmForumsPosting.Show
        End If
End Sub

Basically, I want to update a field in the row that this function was called from.

Does that make sense?

Thanks agian for your help.

g
 
Upvote 0
phxsportz said:
You would use Match and Offset, not Vlookup.

Match will return the row you want to edit
ie ID = 01 Range = A1:A4 Match of "01" returns row 3


Code: 
cells(match(id,Range("A1:A4"),0),4) = "Whatever you want to put in the 4th cell of your Row"
Click to expand...

Ok, this sorta makes sense... I'm going to attempt to modify it for me needs and see if I understand it.

For the below, lets say there is a table like this and the range is named "Employees"

ID Name Status
01 - Bob - Married
02 - Jon - Single
03 - Geo - Married
04 - Joe - Married

Would this code update Joe to be "Single"?

Code: 
cells(match("04",Range("Employees"),0),3) = "Single"
 
Last edited:
Upvote 0
Close..

Code: 
cells(worksheetfunction.MATCH("04",employees,0),3) = "Single"
 
Last edited:
Upvote 0
phxsportz said:
Close..

Code: 
cells(worksheetfunction.MATCH("04",employees,0),3) = "Single"
Click to expand...

Phx,

Thanks for your help!!! I'm glad I found this board. People have been much more helpful than at other boards!!! I'll play with this and post back if I have problems.

g
 
Upvote 0
Ok, here is my code. It returns and error

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

I get the following error:

Run-Time error '1004':

Unable to get the Match property of the WorksheetFunction Class

g
 
Upvote 0
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..

Code: 
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Issue making macro or formula on my spreedsheet
Replies
2
Views
97
devan69
D
C
  • Solved
Updating a specific cell value in an autofiltered table
Replies
3
Views
243
skillilea
S
N
  • Question
Count a value in a cell (within a specified range) every nth cell along the row
2
Replies
17
Views
571
KRice
K
T
  • Locked
  • Question
Updated unanswered question
Replies
2
Views
279
Fluff
Fluff
D
  • Question
Auto-Update Problem
Replies
0
Views
268
Diulama1023
D

Forum statistics

Threads
1,215,241
Messages
6,123,823
Members
449,127
Latest member
Cyko

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top