CODEWITHSUNDEEP
stringexcelvbaformatting
Trimax
Trimax

Reputation: 2473

VBA Excel formatting numbers to strings

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"))

Upvotes: 1

Views: 6616

Answers (2)

scraaappy
scraaappy

Reputation: 2886

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")

Upvotes: 0

L42
L42

Reputation: 19737

You can do that in two(2) ways:

  1. 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

  2. Use ' apostrophe

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

Upvotes: 1

Related Questions