LinkBack URL
About LinkBacksHi 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
Register To Reply
Bookmarks