looking for VBA help regarding if statement based on number format selection

Pilot5000 · Jun 4, 2015

I have a worksheet with the following information
Cell F3 contain drop list for different currencies & percentage
Cell E3 contain the symbol of each currency & percentage according to F3
Column I trough K contain numbers and percentages
Presently I have a code that I got from the web that change the currency symbol based on the selection in Cell E3 in all columns

Code:

Sub Calculate()

Dim myrange As Range
Currency_Symbol = Worksheets("FX rates").Range("E3")
myrange = Worksheets("FX rates").Range("I1", Rows.Count).End(xlUp)

Worksheets("FX rates").Range("I2:I20").NumberFormat = Currency_Symbol & "  #,##0.00;[Red]-(#,##0.00)"

End Sub

I need a code that go through columns I-K and change only the numbers that are with currency symbol and not the number with percentage (%) symbol, i tried to do something but it does not seems to work for me because it's seems too long and complicate and does not work for me . i am sure that there is something more simple then that

Code:

Sub Calculate3()

Currency_Symbol = Worksheets("FX rates").Range("E3")
myrange = Worksheets("FX rates").Range("m2:m20")
For x = endrow To startrow Step -1

For i = 1 To 1000

For Each cell In myrange
If Currency_Symbol = "%; text " Then GoTo nextcell
'Else: Worksheets("FX rates").Range("M2:M20").NumberFormat = Currency_Symbol & "  #,##0.00;[Red]-(#,##0.00)"
If Worksheets("FX rates").Range("F3").Value = "Euro" Then
Worksheets("FX rates").Range("M2:M20").NumberFormat = Currency_Symbol & "  #,##0.00;[Red]-(#,##0.00)"
ElseIf Worksheets("FX rates").Range("F3").Value = "Euro" Then
Worksheets("FX rates").Range("M2:M20").NumberFormat = Currency_Symbol & "  #,##0.00;[Red]-(#,##0.00)"
ElseIf Worksheets("FX rates").Range("F3").Value = "Yan" Then
Worksheets("FX rates").Range("M2:M20").NumberFormat = Currency_Symbol & "  #,##0.00;[Red]-(#,##0.00)"
ElseIf Worksheets("FX rates").Range("F3").Value = "ISL" Then
Worksheets("FX rates").Range("M2:M20").NumberFormat = Currency_Symbol & "  #,##0.00;[Red]-(#,##0.00)"
ElseIf Worksheets("FX rates").Range("F3").Value = "Pound" Then
Worksheets("FX rates").Range("M2:M20").NumberFormat = Currency_Symbol & "  #,##0.00;[Red]-(#,##0.00)"
End If
End If
End Sub

My other issue is when the when number is negative it does not take the currency symbol but only the number, I need to have also the currency symbol
Thank you all for your collaboration

Pilot5000 · Jun 7, 2015

Any help would be greatly appreciated!

Thank you

looking for VBA help regarding if statement based on number format selection

Pilot5000

New Member

Excel Facts

Pilot5000

New Member

Similar threads

Forum statistics

Share this page

looking for VBA help regarding if statement based on number format selection

Pilot5000

New Member

Excel Facts

Pilot5000

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock