Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Cell Loop
I want to reflect the value of one number in terms of another.
For example Euros in terms of Dollars. So in one cell I put the number of Euros and multiply them by the exchange rate of 1.33 to get the amount in Dollars. But now I want to put the number of Dollars and use the same exchange rate to find out how many Euros those Dollars worth. How can I make a cell to use a formula and also to be able to take a number without erasing the formula that is in it? A1 B1 C1 100 Euros 1.33 =A1*B1 which is straight forward But now I want also to be able to put 100 Dollars in cell C1 and have the equivalent amount in Euros in cell A1. Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Cell Loop
Please keep questions in the ng unless invited or you want to become a
paying customer.<G Private Sub Worksheet_Change(ByVal Target As Range) if target.row <2 then exit sub Application.EnableEvents = False If Target.Column = 1 Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Column = 3 Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... right click sheet tabview codeinsert this. I suspect you want for more than ONE set. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$1" Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Address = "$C$1" Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "amirstal" wrote in message ups.com... I want to reflect the value of one number in terms of another. For example Euros in terms of Dollars. So in one cell I put the number of Euros and multiply them by the exchange rate of 1.33 to get the amount in Dollars. But now I want to put the number of Dollars and use the same exchange rate to find out how many Euros those Dollars worth. How can I make a cell to use a formula and also to be able to take a number without erasing the formula that is in it? A1 B1 C1 100 Euros 1.33 =A1*B1 which is straight forward But now I want also to be able to put 100 Dollars in cell C1 and have the equivalent amount in Euros in cell A1. Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Cell Loop
Is that what you meant? Is that how it should look?
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$1" Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Address = "$C$1" Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) if target.row <2 then exit sub Application.EnableEvents = False If Target.Column = 1 Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Column = 3 Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub Thanks. Don Guillett wrote: Please keep questions in the ng unless invited or you want to become a paying customer.<G Private Sub Worksheet_Change(ByVal Target As Range) if target.row <2 then exit sub Application.EnableEvents = False If Target.Column = 1 Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Column = 3 Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... right click sheet tabview codeinsert this. I suspect you want for more than ONE set. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$1" Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Address = "$C$1" Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "amirstal" wrote in message ups.com... I want to reflect the value of one number in terms of another. For example Euros in terms of Dollars. So in one cell I put the number of Euros and multiply them by the exchange rate of 1.33 to get the amount in Dollars. But now I want to put the number of Dollars and use the same exchange rate to find out how many Euros those Dollars worth. How can I make a cell to use a formula and also to be able to take a number without erasing the formula that is in it? A1 B1 C1 100 Euros 1.33 =A1*B1 which is straight forward But now I want also to be able to put 100 Dollars in cell C1 and have the equivalent amount in Euros in cell A1. Thanks, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Cell Loop
The first one works for your ORIGINAL request. The second works for the
entire columns. Did it not work for you? If not, perhaps you did not put in the SHEET module as instructed. Also, is your calculation mode set to automatic? -- Don Guillett SalesAid Software "amirstal" wrote in message ups.com... Is that what you meant? Is that how it should look? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$1" Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Address = "$C$1" Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) if target.row <2 then exit sub Application.EnableEvents = False If Target.Column = 1 Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Column = 3 Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub Thanks. Don Guillett wrote: Please keep questions in the ng unless invited or you want to become a paying customer.<G Private Sub Worksheet_Change(ByVal Target As Range) if target.row <2 then exit sub Application.EnableEvents = False If Target.Column = 1 Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Column = 3 Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... right click sheet tabview codeinsert this. I suspect you want for more than ONE set. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$A$1" Then _ Target.Offset(, 2) = Target.Offset(, 1) * Target If Target.Address = "$C$1" Then _ Target.Offset(, -2) = Target / Target.Offset(, -1) Application.EnableEvents = True End Sub -- Don Guillett SalesAid Software "amirstal" wrote in message ups.com... I want to reflect the value of one number in terms of another. For example Euros in terms of Dollars. So in one cell I put the number of Euros and multiply them by the exchange rate of 1.33 to get the amount in Dollars. But now I want to put the number of Dollars and use the same exchange rate to find out how many Euros those Dollars worth. How can I make a cell to use a formula and also to be able to take a number without erasing the formula that is in it? A1 B1 C1 100 Euros 1.33 =A1*B1 which is straight forward But now I want also to be able to put 100 Dollars in cell C1 and have the equivalent amount in Euros in cell A1. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through last cell/row of used range | Excel Discussion (Misc queries) | |||
VBA Loop until certain cell! | Excel Programming | |||
Loop to add +1 in cell? | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
loop through a cell. | Excel Programming |