VBA将数字格式化为字符串

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

在表1中，值为45，因此我希望在表RESULTADO中采用如下格式: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"))

2 个解决方案

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

#2

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 :

这行不通，因为在第一种情况下，你试图格式化数字n到00n，总是返回n，在第二种情况下，你做的完全一样然后你把结果转换成一个字符串。你必须先把n转换成一个字符串。在你的例子中

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

#1