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]

Right click on the sheet's tab and choose View Code then paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C1:C11")) Is Nothing Then
   Select Case Target.Value
   Case "AC"
   Target.Offset(0, -1).Value = Target.Offset(0, -1).Value * 3
   Case "AD"
   Target.Offset(0, -1).Value = Target.Offset(0, -1).Value * 7
   Case "AB"
   Target.Offset(0, -1).Value = Target.Offset(0, -1).Value * 4
   End Select
Else
   Exit Sub
End If
End Sub

P.S: An essay is A LOT BETTER AND TOO MUCH BETTER than just throwing a quick general question and keep posting to reach the OP's goal!!

ZAX

 

[Goastler]

OH MY GOD IT WORKED THANKYOU!! This has been doing my head in for a fortnight now, i'm so relieved to have it solved now. That has made my day, thank you very much

 

[Goastler]

Okay, it half works. its fine going from AB to AD, say, and multiplying the value upwards, but it doesnt divide when going downwards, AD to AB, it just multiplies further. Would it be possible to have it dividing in the reverse change?
i.e
AB <- AC = Divide by 3
AC <- AD = Divide by 7
AD <- AB = Divide by 4
and also make it aware of what the cell was? it knows whenever the cell is changed to AC, it is to multiply by 3, but id like it to be able to recognise when AB is changed to AC and then multiply by 4. that way a divide line could be made for the other way when AC is changed to AB

Thanks

 

[ZAX]

Right click on the sheet's tab and choose View Code then paste:

[/B]
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Cells(Rows.Count, Columns.Count).Value
Case ""
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
Cells(Rows.Count, Columns.Count).Value = Target.Value
End Sub

P.S: The first time you change a value nothing will happen but then ENJOY!....And thanx for the feed back and the Like
ZAX

 

[Goastler]

Okay, i'm probably doing something wrong, but the code doesnt work. like, at all - nothing happens :L ive put the code on the sheet part of the VBE list and then tried to change some of the AB, etc... to other ones and the values stay the same. then i thought that the Cells(Rows.Count,Columns.Count).Value might need defining, so i made both of them Cells("C1:C11").Value, but that didnt work either. i dont know why it isnt working...

 

[Goastler]

No Worries, ive done it :D changed the Cells(Rows.Count,Columns.Count).Value to Cells(,3).Value and its all working brilliantly, thank you very much

 

[Goastler]

All sorted now, but just one last thing. It's more of a side note than a problem. I've got my textual data in the C column, defined as 3 in the Cells(Rows.Count,Columns.Count).Value. For some reason, the top cell of the column will always equal the very last input into any cell on the sheet. Not sure why, tried the macro on multiple sheets and they all do it. guess its just a side effect? :L

 

[ZAX]

Sorry,Try this:

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 ""
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

ZAX

 

[Goastler]

it isnt doing anything at all now