VBA - Even though i use a TEXT(<range>,"00") formula my outcome will be "0"

[NessPJ]

Hello there,

I am using this code in my VBA routine:

  Application.Workbooks(RTBook).Sheets(ATTargetSht).Range("BL3:BL" & LastRowPVTable).Formula = "=TEXT(MID(BN3,2,2),""00"")"
    Application.Workbooks(RTBook).Sheets(ATTargetSht).Range("BL3:BL" & LastRowPVTable).Formula = Application.Workbooks(RTBook).Sheets(ATTargetSht).Range("BL3:BL" & LastRowPVTable).Value

Even though i use it, some of my data containing a result value of 02, 03, 04 etc. will end up showing like 2, 3, 4.
I would the result to always exist out of 2 digits. What am i doing wrong here?

 

[MARK858]

When you do the .Formula = .Value (btw it should be .Value = .Value) you are converting it from Text to a Real number and so it will pick up whatever the format of the cell is.

Custom format the cell as 00 maybe if it is only a visual you require (although if you are doing that there is no point having the text part of the first formula).

 

[MARK858]

I suppose if you do need the data to actual be 2 digits rather than just appear as 2 digits you could try....

    With Application.Workbooks(RTBook).Sheets(ATTargetSht).Range("BL3:BL" & LastRowPVTable)
        .Formula = "=TEXT(MID(B1,2,2),""00"")"
        .NumberFormat = "@"
        .Value = .Value
    End With