VBA for if statement based on number format selection

Pilot5000 · Jun 2, 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

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, or if I select in F3 percentage (%) it will change only the number with percentage format

My other problems is that when the as you can see column -”I18”, 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

With continuation to my previous thread I tried to resolve that matter and build a macro but it’s not working as I need maybe just a little help and correction of this macro will give me the solution I need, please any help will be appreciated

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

NARAYANK991 · Jun 7, 2015

Hi ,

See if this is OK.

Narayan

Pilot5000 · Jun 8, 2015

NARAYANK991 said:
Hi ,

See if this is OK.

Narayan

dear Narayan

thank you thank you thank you for your help you don't know how much it help me excellent job, i really appreciate that, just some more questions
1. giving the fact that in my project the range go from a2 to z5000 is there is a way to indicate the rage as A2 all the way to the last cell ? (i.e Sheets("sheet1").Range("A2", Cells(Rows.Count, 1).End(xlUp)) something like that,
2. for understanding what is the Abs stand for in your code ?
3. when i run your code on my sheet i get run-time error 13 type mismatch on the line- If Abs(cell.Value) > 1 And Not Percent Then FormatCell = True can you tell me why moreover, it does not change all range only part of it

Code:

Sub Currancy_1()
    Dim Currency_Symbol As String, CellFormat As String
    Dim Percent As Boolean, FormatCell As Boolean
    Dim myrange As Range, cell As Range

    Currency_Symbol = Worksheets("FX rates").Range("E3")
    Percent = (InStr(1, Currency_Symbol, "%") > 0)
    Set myrange = Worksheets("sheet1").Range("A2:H57")

    For Each cell In myrange
        CellFormat = cell.NumberFormat
        FormatCell = False
        If Abs(cell.Value) > 1 And Not Percent Then FormatCell = True
        If Abs(cell.Value) <= 1 And Percent Then FormatCell = True
        If FormatCell Then
          If Percent Then
              cell.NumberFormatLocal = "General " & Currency_Symbol & ";" & "General " & Currency_Symbol
          Else
              cell.NumberFormatLocal = Currency_Symbol & "  #,##0.00;" & Currency_Symbol & "  [Red](#,##0.00)"
          End If
        End If
    Next
End Sub

p.s as you can see i attached the code to show you where i made the change to much my data sheet the only change i made in your code is only the sheet name and the range size you have the sheet1 in the file you send me it;s hidden sheet

i does some checking and i found out why i get the error 13 , if you look in sheet1 columns C, E & G ranges 33 &34 (net assets ) when i move it from my range it does not give the error again i don't know why but i need that there , any way to resolve that matter . thank you again for everything

NARAYANK991 · Jun 8, 2015

Hi ,

See this file for the revision.

I have used the following logic :

If a value is between -1 and +1 , then we consider it a percent value ; if it is beyond these two values , then we consider it a currency value.

Checking for this requires the use of the ABS function , which is short form for ABSOLUTE ; what this does is return the positive value of any number irrespective of whether it is positive or negative ; thus ABS(-5) is the same as ABS(5) is equal to 5.

If it is a percent value , and the selected currency symbol is not a percent symbol , then we do not apply the selected currency symbol to it.

If it is a currency value , and the selected currency symbol is not a percent symbol , then we do apply the selected currency symbol to it.

If it is a percent value , and the selected currency symbol is a percent symbol , then we do apply the selected currency symbol to it.

If it is a currency value , and the selected currency symbol is a percent symbol , then we do not apply the selected currency symbol to it.

Thus , the initial cell format for all the cells can be General , Number or any other format ; selecting the appropriate currency symbol and clicking the button will apply this symbol to the relevant cells ; selecting the percentage symbol , and clicking the button will apply this format to the relevant cells.

Narayan

Pilot5000 · Jun 8, 2015

thank you for the explanation very clear and understanding , i learn something new thank you again,
when i run your code on my sheet i get run-time error 13 type mismatch on the line- If Abs(cell.Value) > 1 And Not Percent Then FormatCell = True can you tell me why moreover, it does not change all range only part of it.
therefore i does some checking and i found out why i get the error 13 , if you look in sheet1(hidden sheet in the workbook, just open it ) columns C, E & G ranges 33 &34 (net assets ) when i move it from Myrange borders it does not give the error again i don't know why but i need that there , any way to resolve that matter . thank you again for everything

secondly

NARAYANK991 · Jun 8, 2015

Hi ,

Sorry but I have not understood under what conditions the Run Time Error type 13 appears. Can you explain with an example ?

Narayan

Pilot5000 · Jun 8, 2015

did you saw the sheet1 that as i explain to you , ? in your file un hide the sheet1 and then you can see my explanation for my problems . i would like to upload the file but i can not do that, it give me option to upload all format file except excel

Pilot5000 · Jun 8, 2015

this is the PDF version print screen of sheet1 where it is highlight "Net Asset " if i move it outside of myrange definition your code work fine but when it there as it's it give me error 13 type mismatch , but i need to have that there because there is a formula there

NARAYANK991 · Jun 8, 2015

Hi ,

So what is the definition of myrange which generates the error message ?

Narayan

Pilot5000 · Jun 8, 2015

hi
i tried to place your code in my project but it still give me the error run-time 13 type mismatch , i attached sampled of PDF how my original file look like , hope you can get from there what i need and how to go around this error message , sorry that i am taking so much of your time but it;s important to me

NARAYANK991 · Jun 8, 2015

Hi ,

I have changed the following 2 lines in the code , and run it on Sheet1 after unhiding it ; there is no problem.

Code:

        endrow = .Cells(Rows.Count, "H").End(xlUp).Row
        Set myrange = Sheet1.Range("B" & startrow & ":" & "H" & endrow)

All I want to know is what is the definition of myrange ? Is it B2 : H56 ?

Narayan

Pilot5000 · Jun 8, 2015

getting from your answer i understand that you open sheet1, if you try to run the macro according the range of data in sheet1(b3-h57) it will give you the error message , but as i explained because i move/or clear content of the data from columns C, E & G ranges 33 &34 (i.e Net Assets & the number under it )to location out of the range i mentioned(b3-h57) it work , myrange in my original project should be F9 to AZ6000,

Pilot5000 · Jun 8, 2015

i have done to the same in my original project but it does not work

NARAYANK991 · Jun 8, 2015

Hi ,

Now I have understood. Copy the revised code from this file.

Narayan

Pilot5000 · Jun 8, 2015

i tired your code in my original file and look what it give me , it changed only in the top the first two rows

Code:

Sub Curr_3()
    Dim Currency_Symbol As String, CellFormat As String
    Dim Percent As Boolean, FormatCell As Boolean
    Dim myrange As Range, cell As Range
    Dim startrow As Long, endrow As Long
 
    With Worksheets("Client Load Inf")
        Currency_Symbol = Sheets("Client Load Inf").Range("E133")
        Percent = (InStr(1, Currency_Symbol, "%") > 0)
        startrow = 9
        endrow = .Cells(Rows.Count, "AZ").End(xlUp).Row
        Set myrange = Sheets("Data Input Area").Range("F" & startrow & ":" & "AZ" & endrow)
    End With
 
    For Each cell In myrange
        CellFormat = cell.NumberFormat
        If IsNumeric(cell.Value) Then
          FormatCell = False
          If Abs(cell.Value) > 1 And Not Percent Then FormatCell = True
          If Abs(cell.Value) <= 1 And Percent Then FormatCell = True
          If FormatCell Then
              If Percent Then
                cell.NumberFormatLocal = "0.00 " & Currency_Symbol
              Else
                cell.NumberFormatLocal = Currency_Symbol & "  #,##0.00;" & Currency_Symbol & "  [Red](#,##0.00)"
              End If
          End If
        End If
    Next
End Sub

this is the code with the sheets name as it in my project

NARAYANK991 · Jun 8, 2015

Hi ,

Try this code :

Code:

Sub Calculate3()
    Dim Currency_Symbol As String, CellFormat As String
    Dim Percent As Boolean, FormatCell As Boolean
    Dim myrange As Range, cell As Range
    Dim startrow As Long, endrow As Long
   
    Currency_Symbol = Worksheets("Client Load Inf").Range("E133")
    Percent = (InStr(1, Currency_Symbol, "%") > 0)
   
    With Worksheets("Data Input Area")
        startrow = 9
        endrow = .Cells(Rows.Count, "AZ").End(xlUp).Row
        Set myrange = .Range("F" & startrow & ":" & "AZ" & endrow)
    End With
   
    For Each cell In myrange
        CellFormat = cell.NumberFormat
        If IsNumeric(cell.Value) Then
          FormatCell = False
          If Abs(cell.Value) > 1 And Not Percent Then FormatCell = True
          If Abs(cell.Value) <= 1 And Percent Then FormatCell = True
          If FormatCell Then
              If Percent Then
                cell.NumberFormatLocal = "0.00 " & Currency_Symbol
              Else
                cell.NumberFormatLocal = Currency_Symbol & "  #,##0.00;" & Currency_Symbol & "  [Red](#,##0.00)"
              End If
          End If
        End If
    Next
End Sub

Narayan

Pilot5000 · Jun 8, 2015

tried it still change only the first two line as you saw from the PDF i just sent you, it not giving me the error message but it's only does not change for the appropriate/selected currency

NARAYANK991 · Jun 8, 2015

Hi ,

In which case , myrange must be getting assigned F9:AZ11 ; why don't you try assigning the entire range of F9:AZ6000 to myrange ?

Narayan

Pilot5000 · Jun 8, 2015

i tried as you said

Pilot5000 · Jun 8, 2015

but it take it for ever to change the symbol, and it didn't change more then the two first rows

Pilot5000 · Jun 8, 2015

i am so sorry that it take so much of your time , please please i need your help

NARAYANK991 · Jun 9, 2015

Hi ,

Since the problem is in some way related to the data in your file , the only way it can be resolved is if you can upload at least some of the data. Since you say the first 2 rows of data are being properly handled , can you see what is different in the data from the 3rd row onwards ?

Why don't you upload a file with say the first 10 rows of data ? If all these 10 rows are correctly processed , it is likely that the remaining will also be handled correctly.

Narayan

Pilot5000 · Jun 9, 2015

i agreed with you and i don't have any problems to send you the file, but as i mentioned i can not upload an excel file here ,don't know why , when i click on upload file it give me the option to select only files with format of word , PDF , PIC and all other file , but not excel i don't know how to explain that or why it's like that , (maybe you have the answer) , last time as you remember i sent send the file Via email to you , do you want me to send it Via email again ??

NARAYANK991 · Jun 9, 2015

Hi ,

I am at a loss to understand why you cannot upload an Excel file i.e. a file with a .xls , .xlsx or .xlsm extension , since these are file extensions which are common amongst attachments on this forum.

One easy way to bypass your problem is to rename your .xlsm file to .pdf and upload it ; once I download your .pdf file , I will rename it back to .xlsm

Narayan

VBA for if statement based on number format selection

New Member

Attachments

New Member

Excel Ninja

Attachments

New Member

Excel Ninja

Attachments

New Member

Excel Ninja

New Member

New Member

Attachments

Excel Ninja

New Member

Attachments

Excel Ninja

New Member

New Member

Excel Ninja

Attachments

New Member

Attachments

Excel Ninja

New Member

Excel Ninja

New Member

New Member

New Member

Excel Ninja

New Member

Excel Ninja