Excel Macro Programming [Solved/Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,
It's been a while since I last wrote my own macro and I can't remember how to do this one up.

I have a pull down list in excel, using data valadation. However, I need to have a macro that will update a second cell with a corresponding number based on the details that are selected in the first cell.

For example:

Pull Down List [F4] Numerical Cell [L4]
Cookies Cookies changes the value of [L4] to 41
Fries Fries changes the value of [L4] to 47
Chocolate Chocolate changes the value of [L4] to 42
Sex Sex changes the value of [L4] to 45
Books Books changes the value of [L4] to 49
Computers Computers changes the value of [L4] to 43

Can someone help me with this?

Thanks.

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
in F4 you have validation list cookies, fries etc.

now right click the sheet tab and click view code
in the resulting window copy paste this event code
save the file

now go to F4 choose from the list and see what happens in L4

post back comments

the event code is

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$4" Then Exit Sub
Dim rf As Range, rl As Range
Set rf = Range("F4")
Set rl = Range("L4")
If rf = "cookies" Then rl = 41
If rf = "fries" Then rl = 47
If rf = "chocolate" Then rl = 42
If rf = "sex" Then rl = 45
If rf = "nooks" Then rl = 49
If rf = "computers" Then rl = 43
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!