Excel Macro Programming

Solved/Closed
DinamoCalypso - Feb 8, 2010 at 10:00 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Feb 8, 2010 at 09:38 PM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Feb 8, 2010 at 09:38 PM
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