Pbl calculating US$ values

J

Joseph

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
 
C

ClémentMarcotte

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


Joseph said:
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
Click to expand...
 
J

Joseph

Bonjour et merci de ta reponse.

' Enonce du probleme:
'
' Column 5 / Row 3 contient le Budget de depart en US Dollars - La
seule valeur statique de la feuille.
' Column 6 / Row 3 contient le Budget de depart convertie en Livres
anglaises
' Column 4 = D => Valeur d'une depense
' Column 5 = E => le Budget de depart en US Dollars - la Valeur
d'une depense
' Column 6 = F => meme chose mais en livre anglaise

Le probleme. Lorsque j'entre une valeur precedee du symbole £ (UK
pounds) sign dans la colonne 4, alors les Colonnes 5 & 6 (US $ & UK £)
montre les resultats correctes.

Par contre si la valeur entree dans la colonne 4 est precedee du $ sign,
les colonnes 5 et 6 affichent une erreur: #VALUE.

Ci-dessous se trouve la procedure entiere
- E5 = $50.000.00 par example et la seule valeur statique.
- F5 = E5 * 1.65 - une valeur arbitraire pour convertir les $ en £ UK
pounds = £30,303.03 - Resultat expecte - ok.

Ensuite, si:
D6 = £1000 alors E6 = $48.350.00 et F6 = £29,303.03
MAIS si j'essaie avec des dollars:
D6 = $1000 alors les 2 colonnes E6 & F6 affichent l'erreur:= #VALUE

Je ne comoprends pas pourquoi
Col E est Formatee en champ monetaire US($) & Col F en UK(£)

voila.

Cordialement.

Joseph
http://www.geocities.com/philippeoget/a2z/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Comparing text instead of values? 1
Weird range selection issue XL2003- help please! 3
Ws Selection Change Event Code, Copy a Cell problem 3
Macro to match 2 column values in different sheets 1
Worksheet_Change help please 3
converting a for next loop into a for each loop 2
Scrabble Value calculation for Welsh words 0
Transfer array element offset value to a matched value 3

Top