On cell change - run macro
Posted by Artem on April 09, 2001 11:01 AM
hello,
sorry for bugging you guys with frequent requests, but if you don't mind a short question:
i have a disjoint range of data named "Revenues". Those revenues are usually in foreign currency, so i also have a cell named "FX" which has an exchange rate. Whenever the "FX" cell is changed, i want the new values in range "Revenue" to be their old values times "FX" value (basically converting all numbers from one currency to another). unfortunately, i don't know how to make Excel store old values. PLEASE HELP!!! i know this problem can be solved with a simple formula, but that's not exacly applicable in my case). below is my non-working macro in case you need it.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
OldValue = Range("Revenues").Value
If Target.Address = "FX" Then
Range("Revenues").Value = OldValue * Range("FX").Value
End Sub
Posted by John on April 09, 2001 12:31 PM
How about:
Range("fx").Copy
Range("revenues").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
Posted by Artem on April 09, 2001 12:44 PM
Doesn't work...
thanks for help John, but i'm too stupid to make it work. it does work as is, yet i can't tie it to the cell change event. here's what i'm trying to do:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "FX" Then
Range("FX").Copy
Range("Revenues").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
End Sub
Posted by John on April 09, 2001 1:29 PM
Re: Doesn't work...
Try saving the file, closing it and opening it again.
Posted by Artem on April 09, 2001 2:04 PM
Still doesn't work
sorry for bugging you but it still doesn't work
i tried:
1. saving, closing, and re-opening
2. putting code in Sheet1
3. putting code in ThisWorkbook
4. putting code in separate module
nothing works. maybe there should be some Declaration somewhere? thanks a lot for help!
Posted by John on April 09, 2001 3:02 PM
Re: Still doesn't work
Sorry, I was looking at all your code.
If Target = range("fx") Then
Range("FX").Copy
Range("Revenues").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
Posted by Artem on April 09, 2001 4:09 PM
Almost works!
hey John it almost works, just gives me a run-time error '13' Type mismatch. do you know what that might be? otherwise it does what it should, except for this error. thanks for all your help!
Posted by John on April 09, 2001 5:31 PM
Re: Almost works!
OK, this should do it:
If Target.Address = Range("fx").Address Then
Range("FX").Copy
Range("Revenues").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
End If
Posted by Artem on April 09, 2001 6:20 PM
Yoo-hoo! THANKS!!!
It's great!!!
