Hi all,

' Premises:
'
' Column 5 / Row 3 contains the Starting Budget in US Dollars - The only
Static value
' Column 6 / Row 3 is the Starting Budget in $US converted to UK Pounds
' Column 4 = D = Order Value
' Column 5 = E = Remaining Budget Amount in US Dollars
' Column 6 = F = Remaining Budget Amount in UK Pounds

Here is the problem. When I enter a value preceded by a £ (UK pounds) sign
in Col 4
then Cols 5 & 6 (US $ & UK £) respectively calculate as expected

If I enter a value preceded by a $ sign, I get #VALUE errors.

Below is the complete Sub so you should be able
to reproduce the problem relatively easily
using the Premises above.
- E5 = $50.000.00 for example and this is the only static value, then
- F5 = E5 * 1.65 - arbitratry value used to convert to UK pounds =
£30,303.03 - OK so far.

From there, if:
D6 = £1000 then E6 = $48.350.00 and F6 = £29,303.03
But if I try:
D6 = $1000 then E6 & F6 = #VALUE

I don't understand why.
Incidentally Col E is Formatted as currency US($) & Col F as UK(£)

I'll appreciate your help, thank you.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
' Negative values are not permitted
' in Order Value (Dn) where n = ActiveRow number
' or in the Starting Budget(E3)
'

' Detect the Active Row Number
Dim actRow As Integer
actRow = ActiveCell.Row

If Sh.Name = "Sheet1" Then

' Set the value in UK Pounds in Column F from
' the value in the previous column which is in US Dollars

' Value must be greater than 0, no negative value permitted

If Cells(3, 5).Value 0 Then ' Row 3, Column 5 (E) = Budget in
US Dollars
' (The only Static value)

' Row 3 Column 6(F).value = Row 3, Column 5(E).value / 1.65
' Convert the US Budget into UK Pounds
Range("F3").Formula = "=$E$3 / 1.65"

ElseIf Cells(3, 5).Value <= 0 Then

MsgBox "Sorry 0 or negative values are not permitted" _
& Chr(13) _
& "Please enter a value greater than 0." _
, vbExclamation + vbOKOnly, "Value Check"
Cells(3, 5).Activate
Exit Sub

End If

' Cell in ActiveRow/Column 4(D).value 0
' - Do not permit negative value

If Cells(actRow, 4).Value 0 Then

Cells(actRow, 4).NumberFormat = "@" ' Text

' If no currency sign was used
' pre-pend the value entered with a £ sign
If Left(Cells(actRow, 4).Value, 1) Like "[0-9]" Then _
Cells(actRow, 4).Value = "£" & Cells(actRow,
4).Value

' And change the currency style accordingly
If Left(Cells(actRow, 4).Value, 1) = "£" Then

Cells(actRow, 4).NumberFormat = "[$£-809]#,##0.00"

ElseIf Left(Cells(actRow, 4).Value, 1) = "$" Then

Cells(actRow, 4).NumberFormat = "[$$-409]#,##0.00"

End If

Else ' Value is negative, but we ignore blanks...

If Trim(Cells(actRow, 4).Value) < "" And Cells(actRow, 4).Value
< 0 Then

MsgBox "Sorry 0 or negative values are not permitted" _
& Chr(13) _
& "Please enter a value greater than 0." _
, vbExclamation + vbOKOnly, "Value Check"
Cells(actRow, 4).Activate
Exit Sub

End If
End If

'************************************************* ******
'
' The Problem is here, somewhere
'
' As long as the value entered in D+currentRow
' is preceded by a UK Pound (£) sign
' the calculations take place ok
'
' However
'
' Entering value preceded by a US Dollar ($) sign causes an error
' and both cells in column F & E display a #VALUE error
' Cant imagine why
'
'************************************************* ******
' Column 4 = D = Order Value
' Column 5 = E = Remaining Budget Amount in US Dollars
' Column 6 = F = Remaining Budget Amount in UK Pounds

If Left(Cells(actRow, 4).Value, 1) = "£" Then

Range("E" & actRow).Formula = _
"=$E$" & (actRow - 1) & " - ($D$" & actRow & " *
1.65)"

Range("F" & actRow).Formula = _
"=$F$" & (actRow - 1) & " - $D$" & actRow

ElseIf Left(Cells(actRow, 4).Value, 1) = "$" Then

Range("E" & actRow).Formula = _
"=$E$" & (actRow - 1) & " - $D$" & actRow

Range("F" & actRow).Formula = _
"=$F$" & (actRow - 1) & " - ($D$" & actRow & " /
1.65)"
End If

'Cells(actRow, 4).Activate


End If ' If Sh.Name = "Sheet1"

'Application.StatusBar = "actRow: " & actRow

End Sub

Philippe Oget, c'est un nom français, écris en français, joualvert.


"Joseph" a écrit dans le message de
...
Hi all,

' Premises:
'
' Column 5 / Row 3 contains the Starting Budget in US Dollars - The
only
Static value
' Column 6 / Row 3 is the Starting Budget in $US converted to UK
Pounds
' Column 4 = D = Order Value
' Column 5 = E = Remaining Budget Amount in US Dollars
' Column 6 = F = Remaining Budget Amount in UK Pounds

Here is the problem. When I enter a value preceded by a £ (UK pounds)
sign
in Col 4
then Cols 5 & 6 (US $ & UK £) respectively calculate as expected

If I enter a value preceded by a $ sign, I get #VALUE errors.

Below is the complete Sub so you should be able
to reproduce the problem relatively easily
using the Premises above.
- E5 = $50.000.00 for example and this is the only static value, then
- F5 = E5 * 1.65 - arbitratry value used to convert to UK pounds =
£30,303.03 - OK so far.

From there, if:
D6 = £1000 then E6 = $48.350.00 and F6 = £29,303.03
But if I try:
D6 = $1000 then E6 & F6 = #VALUE

I don't understand why.
Incidentally Col E is Formatted as currency US($) & Col F as UK(£)

I'll appreciate your help, thank you.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
' Negative values are not permitted
' in Order Value (Dn) where n = ActiveRow number
' or in the Starting Budget(E3)
'

' Detect the Active Row Number
Dim actRow As Integer
actRow = ActiveCell.Row

If Sh.Name = "Sheet1" Then

' Set the value in UK Pounds in Column F from
' the value in the previous column which is in US Dollars

' Value must be greater than 0, no negative value permitted

If Cells(3, 5).Value 0 Then ' Row 3, Column 5 (E) = Budget
in
US Dollars
' (The only Static value)

' Row 3 Column 6(F).value = Row 3, Column 5(E).value / 1.65
' Convert the US Budget into UK Pounds
Range("F3").Formula = "=$E$3 / 1.65"

ElseIf Cells(3, 5).Value <= 0 Then

MsgBox "Sorry 0 or negative values are not permitted" _
& Chr(13) _
& "Please enter a value greater than 0." _
, vbExclamation + vbOKOnly, "Value Check"
Cells(3, 5).Activate
Exit Sub

End If

' Cell in ActiveRow/Column 4(D).value 0
' - Do not permit negative value

If Cells(actRow, 4).Value 0 Then

Cells(actRow, 4).NumberFormat = "@" ' Text

' If no currency sign was used
' pre-pend the value entered with a £ sign
If Left(Cells(actRow, 4).Value, 1) Like "[0-9]" Then _
Cells(actRow, 4).Value = "£" & Cells(actRow,
4).Value

' And change the currency style accordingly
If Left(Cells(actRow, 4).Value, 1) = "£" Then

Cells(actRow, 4).NumberFormat = "[$£-809]#,##0.00"

ElseIf Left(Cells(actRow, 4).Value, 1) = "$" Then

Cells(actRow, 4).NumberFormat = "[$$-409]#,##0.00"

End If

Else ' Value is negative, but we ignore blanks...

If Trim(Cells(actRow, 4).Value) < "" And Cells(actRow,
4).Value
< 0 Then

MsgBox "Sorry 0 or negative values are not permitted"
_
& Chr(13) _
& "Please enter a value greater than 0." _
, vbExclamation + vbOKOnly, "Value Check"
Cells(actRow, 4).Activate
Exit Sub

End If
End If

'************************************************* ******
'
' The Problem is here, somewhere
'
' As long as the value entered in D+currentRow
' is preceded by a UK Pound (£) sign
' the calculations take place ok
'
' However
'
' Entering value preceded by a US Dollar ($) sign causes an error
' and both cells in column F & E display a #VALUE error
' Cant imagine why
'
'************************************************* ******
' Column 4 = D = Order Value
' Column 5 = E = Remaining Budget Amount in US Dollars
' Column 6 = F = Remaining Budget Amount in UK Pounds

If Left(Cells(actRow, 4).Value, 1) = "£" Then

Range("E" & actRow).Formula = _
"=$E$" & (actRow - 1) & " - ($D$" & actRow & "
*
1.65)"

Range("F" & actRow).Formula = _
"=$F$" & (actRow - 1) & " - $D$" & actRow

ElseIf Left(Cells(actRow, 4).Value, 1) = "$" Then

Range("E" & actRow).Formula = _
"=$E$" & (actRow - 1) & " - $D$" & actRow

Range("F" & actRow).Formula = _
"=$F$" & (actRow - 1) & " - ($D$" & actRow & "
/
1.65)"
End If

'Cells(actRow, 4).Activate


End If ' If Sh.Name = "Sheet1"

'Application.StatusBar = "actRow: " & actRow

End Sub