VBA Text() function not formatting correctly

[nathanpere]

Hello everyone,

I am new to writing in the VBA and have been using the WorksheetFunction.Text() command to format data. The data I am using is taken from PowerBI and for some reason my text function will not work correctly.

Sub FormatAsText()

    Dim cel As Range, rng_percent As Range, rng_minute As Range
    Set rng_percent = Range("B3,D3:E3,B7,D7:E7,B8,D8:E8,B13,D13:E13")
    Set rng_minute = Range("B12,D12:E12,B5,D5:E5,B6,D6:E6")
    
        For Each cel In rng_percent
            With cel
                cel.Value = WorksheetFunction.Text(cel.Value, "0.0%")
            End With
        Next cel
        
         For Each cel In rng_minute
            With cel
                cel.Value = WorksheetFunction.Text(cel.Value, "mm:ss")
            End With
        Next cel

End Sub

This is the code I am using right now, however the output of my text still seems to be in the format "0.00%" as shown in the image below.

Does anybody know why this is and how I can change it to the format of 1 decimal point?

Also if you have any ways to make my code more efficient it would be much appreciated.

Thanks!

 

[Phuoc]

Try this

For Each cel In rng_percent
            With cel
                .NumberFormat = "0.00%"
            End With
        Next cel
        
         For Each cel In rng_minute
            With cel
               .NumberFormat = "h:mm"
            End With
        Next cel

 

[nathanpere]

Thank you, Phuoc. That seemed to do the trick.

 

[Phuoc]

A little shorter:

Sub FormatAsText()

    Dim rng_percent As Range, rng_minute As Range
    Set rng_percent = Range("B3,D3:E3,B7,D7:E7,B8,D8:E8,B13,D13:E13")
    Set rng_minute = Range("B12,D12:E12,B5,D5:E5,B6,D6:E6")
   
    rng_percent.NumberFormat = "0.00%"
    rng_minute.NumberFormat = "h:mm"

End Sub