Can I use TEXT function with Evaluate in VBA

Hello

I am trying to come up with code that will be used on a range of client numbers expressed as integers like 100 and 2020 and change them into six character text values expressed like 000100 and 002020.

I am trying to alter this trim code that works great to fit my needs:

Please Login or Register  to view this content.

I thought I could alter the above code to this but it doesn't do anything:

Please Login or Register  to view this content.

I would be forever grateful if you have any ideas. I would prefer not to incorporate a loop if I don't have to because some of my worksheets have a hundred thousand plus rows and that will take forever to work through them all. Evaluate seems to handle that many rows easily with the trim function above.

Thank you so much.
T

VBA equivalent of =TEXT(A1,"000000") :

Please Login or Register  to view this content.

Why are you using evaluate instead of range.formula?

Do you want the formula to be placed in the cell or just the value placed in the cell?

If your code places the formula into the cell, FORMAT() wont work as its a VBA function, not a worksheet function.

Is the formula already in the cells and you want to just add this formatting to them? Does putting TEXT in as you have it work if you manually add it to a formula on the sheet (not using the macro)?

You might also simply set the numberformat of the range to @ before using your original Evaluate statement with TEXT.