Changing value of a cell by change of value in another cell using a drop down list

[Goastler]

Hi,
I have a spreadsheet with numerical data in B1:B11 and textual data in C1:C11. The numerical data is a couple of digits, nothing big, and the textual data is always two letters, in this case AB, AC or AD. I have the options for AB, AC or AD for each of the cells in C1:C11 in a data validation drop down list. What I'm trying to do is make a VBA macro, of some sort, that acts upon when, AB for instance, is changed to AC, and then multiply the adjacent numerical value in the B column by a conversion value:
AB -> AC = 3
AC -> AD = 7
AD -> AB = 4

So for example, if I had cell B2 as 10 and C2 as AB, then changed AB to AC using the drop down menu, the macro would recognise the change, select the adjacent cell, B2, and multiply it by 3 to get 30, and put 30 in B2.

Sorry for the essay - kind of difficult to explain

This is what I've got so far, and I'm no VBA expert, so I've probably made mistakes in multiple places. At the moment, the code below is getting stuck on active.celloffset - and I'm really not sure why. I did manage to get it working with specific cells rather than a range, but that only ever worked once and I had to close and re-open excel to get it to work again. Is my code any good, or am I coming at this from the wrong direction? :L help?!

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)[/FONT][/SIZE][SIZE=3][FONT=trebuchet ms]
[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]Static ov As Variant[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]    For Each Cell In Range("B1:B10")[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]    If IsEmpty(ov) Then ov = Cell.Value[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]    If Cell = "AB" And ov = "AC" Then[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]    Application.EnableEvents = False[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]
[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]ActiveCell.Offset(0, -1).Select[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]ActiveCell.Value = ActiveCell.Value * 3[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]    ov = Empty[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]    Else[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]    ov = Empty[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]    [/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]End If[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]Next[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]Application.EnableEvents = True[/FONT][/SIZE]
[SIZE=3][FONT=trebuchet ms]End Sub

 

[ZAX]

Accept my apologies and use the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
Select Case Target.Offset(Rows.Count - Cells(Rows.Count, "C").End(xlUp).Row, Columns.Count - Target.Column).Value
Case ""
Target.Offset(Rows.Count - Cells(Rows.Count, "C").End(xlUp).Row, Columns.Count - Target.Column).Value = Target.Value
Exit Sub
Case "AB"
If Target.Value = "AC" Then
   Target.Offset(, -1).Value = Target.Offset(, -1).Value * 3
ElseIf Target.Value = "AD" Then
   Target.Offset(, -1).Value = Target.Offset(, -1).Value / 4
End If
Case "AC"
If Target.Value = "AB" Then
   Target.Offset(, -1).Value = Target.Offset(, -1).Value / 3
ElseIf Target.Value = "AD" Then
   Target.Offset(, -1).Value = Target.Offset(, -1).Value * 7
End If
Case "AD"
If Target.Value = "AC" Then
   Target.Offset(, -1).Value = Target.Offset(, -1).Value / 7
ElseIf Target.Value = "AB" Then
   Target.Offset(, -1).Value = Target.Offset(, -1).Value * 4
End If
End Select
Target.Offset(Rows.Count - Cells(Rows.Count, "C").End(xlUp).Row, Columns.Count - Target.Column).Value = Target.Value
End Sub

Can you post the results ASAP?
How dm I am!
ZAX

 

[Goastler]

At the moment it looks as if it is working :D ive only had it going 5 minutes and i'll carry on playing, but it is multiplying and dividing relevantly dependant upon the change of AB, AC or AD. Thank you very much, looks like problem solved!

 

[Goastler]

Yep, everything is working like a charm now :D Got there in the end! Cant thank you enough!

 

[ZAX]

Yep, everything is working like a charm now :D Got there in the end! Cant thank you enough!

Well,Glad to get it to help you after 13 posts
You're welcome! and Thanx for the feed back...