VBA Excel formatting numbers to strings

Question

In sheet 1 the value is 45 so I want in sheet RESULTADO the format like this: 0045

Worksheets("RESULTADO").Range("B" & row_counter) = Format(Worksheets(1).Range("B" & row_counter).Value, "0000")

Why this doesn't work?

I've tried also this, but neither it works:

Worksheets("RESULTADO").Range("B" & row_counter) = CStr(Format(Worksheets(1).Range("B" & row_counter).Value, "0000"))

L42 · Accepted Answer · 2015-04-06 10:07:22Z

1

You can do that in two(2) ways:

Set the number format first

With Worksheets("RESULTADO").Range("B" & row_counter)
    .NumberFormat = "@"
    .Value = Format(Worksheets(1).Range("B" & row_counter).Value, "0000")
End With

Use ' apostrophe

Worksheets("RESULTADO").Range("B" & row_counter) = "'" & _
    Format(Worksheets(1).Range("B" & row_counter).Value, "0000")

answered Apr 6, 2015 at 10:07

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

scraaappy · Accepted Answer · 2015-04-06 10:34:26Z

0

this doesn't work because in the first case you're trying to format a number n to 00n wich always return n, in the second case, you do exactly the same then you transtype the result to a string. You have to convert n to a string first. So in your case :

Worksheets("RESULTADO").Range("B" & row_counter) = Format(CStr(Worksheets(1).Range("B" & row_counter).Value), "0000")

answered Apr 6, 2015 at 10:34

scraaappy

2,8862 gold badges21 silver badges29 bronze badges

Collectives™ on Stack Overflow

VBA Excel formatting numbers to strings

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related