mandareds2004
New Member
- Joined
- Dec 21, 2004
- Messages
- 2
I am trying to modify the decimal places based on the text of another cell:
Cell A140 can read either:
Line 1 - "Price to Achieve Financial Goals ($/kWh):"
Line 2 - "Price to Achieve Financial Goals ($/million Btu):"
Line 3 - "Price to Achieve Financial Goals ($/gallon):"
Cell E140 reads different cells based on the correct units:
Cell 'INP-OUT'!$G$51 is in E140 if A140 is Line 1
Cell 'INP-OUT'!$G$50 is in E140 if A140 is Line 2
Cell 'INP-OUT'!$G$49 is in E140 if A140 is Line 3
If the contents of cell A140 equals line 1, I want the result of the equation in cell E140 to show 4 decimal places, with precision: (e.g. 0.3520)
If the contents of cell A140 equals line 2, I want the equation in cell E140 to be 2 decimal places and comma separated with precision: (e.g. 1,000.26)
If the contents of cell A140 equals line 1, I want the equation in cell E140 to be 3 decimal places with precision: (e.g. 0.210)
I have tried a Macro I saw also on this site (see below) but I can't get the commas to work. I am open to a different approach as well if someone suggests it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fmt As String
If Target.Address(0, 0) = "A140" Then
If Target.Value = "Price to Achieve Financial Goals ($/kWh):" Then
fmt = "0.0000"
End If
If Target.Value = "Price to Achieve Financial Goals ($/million Btu):" Then
fmt = "#,##0.00"
Else
fmt = "0.000"
End If
Range("E140").NumberFormat = fmt
End If
End Sub
Thanks!
Cell A140 can read either:
Line 1 - "Price to Achieve Financial Goals ($/kWh):"
Line 2 - "Price to Achieve Financial Goals ($/million Btu):"
Line 3 - "Price to Achieve Financial Goals ($/gallon):"
Cell E140 reads different cells based on the correct units:
Cell 'INP-OUT'!$G$51 is in E140 if A140 is Line 1
Cell 'INP-OUT'!$G$50 is in E140 if A140 is Line 2
Cell 'INP-OUT'!$G$49 is in E140 if A140 is Line 3
If the contents of cell A140 equals line 1, I want the result of the equation in cell E140 to show 4 decimal places, with precision: (e.g. 0.3520)
If the contents of cell A140 equals line 2, I want the equation in cell E140 to be 2 decimal places and comma separated with precision: (e.g. 1,000.26)
If the contents of cell A140 equals line 1, I want the equation in cell E140 to be 3 decimal places with precision: (e.g. 0.210)
I have tried a Macro I saw also on this site (see below) but I can't get the commas to work. I am open to a different approach as well if someone suggests it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fmt As String
If Target.Address(0, 0) = "A140" Then
If Target.Value = "Price to Achieve Financial Goals ($/kWh):" Then
fmt = "0.0000"
End If
If Target.Value = "Price to Achieve Financial Goals ($/million Btu):" Then
fmt = "#,##0.00"
Else
fmt = "0.000"
End If
Range("E140").NumberFormat = fmt
End If
End Sub
Thanks!