Macro to change excel cells to currency but as text

[Malcolm]

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

 

[YasserKhalil]

Try this code

Sub Test()
    With Range("A2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
        .NumberFormat = "£0.00"
    End With
End Sub

 

[NARAYANK991]

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

 

[YasserKhalil]

Try this code as I didn't notice you need to format as text

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

 

[Deepak]

Try this code as I didn't notice you need to format as text

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.

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

 

[YasserKhalil]

I knew in myself Mr. Deepak that you will correct it for me
I'm really learning alot from you
Thank you very much

 

[Malcolm]

In this case this would be simple.

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

 

[YasserKhalil]

Mr. Malcolm , I think it will be better to upload a sample of your desired range