• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to change excel cells to currency but as text

Malcolm

New Member
I have 5 columns in my spreadsheet that I manually change to currency text. I use helper columns and use formula =text(a2,"£0.00")

Could someone provide me a vba script to do this? The number of cells changes so it needs to look at the whole rows
 
Try this code
Code:
Sub Test()
    With Range("A2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
        .NumberFormat = "£0.00"
    End With
End Sub
 
Hi Malcolm ,

Could you provide more details , and if possible upload your workbook ?

1. How are you going to run the macro ? Should the ranges be hard-coded into the macro , or will you select the relevant ranges and then execute the macro , so that the macro operates on the selected ranges ?

2. Are the columns contiguous columns ? Can you specify them ?

3. What does the following mean ?
The number of cells changes so it needs to look at the whole rows
Can you illustrate with an example ?

4. Why do you want to convert numeric quantities to text ? Surely you can apply the currency format to the columns themselves , without using 5 additional columns to convert the numeric quantities to text ?

5. Do you want that the macro should put the formula in the cells or should it put values ?

Narayan
 
Try this code as I didn't notice you need to format as text
Code:
Sub Test()
    Dim rCell As Range
    For Each rCell In Range("A2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
        With rCell
            .Formula = "=TEXT(" & rCell.Value & ",""£0.00"")"
            .Value = .Text
        End With
    Next rCell
End Sub
 
Try this code as I didn't notice you need to format as text
Code:
Sub Test()
    Dim rCell As Range
    For Each rCell In Range("A2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
        With rCell
            .Formula = "=TEXT(" & rCell.Value & ",""£0.00"")"
            .Value = .Text
        End With
    Next rCell
End Sub

In this case this would be simple.

Code:
Sub Test1()
Dim rCell As Range
    For Each rCell In Selection ' Range("A2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
        rCell.Value = CStr(Format(rCell, "£0.00"))
    Next
End Sub
 
I knew in myself Mr. Deepak that you will correct it for me
I'm really learning alot from you
Thank you very much
 
In this case this would be simple.

Code:
Sub Test1()
Dim rCell As Range
    For Each rCell In Selection ' Range("A2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
        rCell.Value = CStr(Format(rCell, "£0.00"))
    Next
End Sub
could you alter this so that rows e,f j and i are changed? Apologies but I am a beginner
 
Back
Top