在 R1C1 公式中使用时，带有十进制的变量将转换为 2 个数字

Question

所以我的问题很简单。在我的 VBA 代码中，我正在从 3 个单元格中检索 3 个值。 在下面的示例中，值 1 = 110,5;值 2=100;值 3=120

Value1 = Worksheets(Countryname).Cells(k, 5).Value
Value2 = Worksheets(Countryname).Cells(k, 14).Value
Value3 = Worksheets(Countryname).Cells(k, 23).Value
Cells(k, 4).Formula = "=MIN(" & Value1 & "," & Value2 & "," & Value3 & ")"

由于未知原因，Excel中显示的结果如下：

=MIN(110;50;100;130), instead of MIN(110,5;100;130)

问题来自第一个变量转换为 2 个变量(110,5 转换为 110;5(

你对这个问题有什么解决方案吗？

提前感谢您的帮助！

Answer 1

逗号作为十进制占位符与默认的 EN-US 列表分隔符冲突。用。公式本地 并按照工作表上显示的方式编写公式。

dim value1 as string, value2 as string, value3 as string
Value1 = Worksheets(Countryname).Cells(k, 5).text
Value2 = Worksheets(Countryname).Cells(k, 14).text
Value3 = Worksheets(Countryname).Cells(k, 23).text
Cells(k, 4).FormulaLocal = "=MIN(" & Value1 & ";" & Value2 & ";" & Value3 & ")"
'alternate with qualified cell addresses
Cells(k, 4).formula = "=min(" & Worksheets(Countryname).Cells(k, 5).address(external:=true) & "," & _
Worksheets(Countryname).Cells(k, 14).address(external:=true) & "," & _
Worksheets(Countryname).Cells(k, 23).address(external:=true) & ")"

看看你使用k的方式，很容易推断出你正在运行一个像for k=2 to lastRow这样的循环。如果是这种情况，请一次编写所有公式。

with range(Cells(2, 4), cells(lastRow, 4))
.formula = "=min(" & Worksheets(Countryname).Cells(2, 5).address(0, 1, external:=true) & "," & _
Worksheets(Countryname).Cells(2, 14).address(0, 1, external:=true) & "," & _
Worksheets(Countryname).Cells(2, 23).address(0, 1, external:=true) & ")"
end with

如果要将值硬编码到工作表公式中，则不妨将结果值写入其中。

Cells(k, 4) = application.min(Worksheets(Countryname).Cells(k, 5).value2, _
Worksheets(Countryname).Cells(k, 14).value2, _
Worksheets(Countryname).Cells(k, 23).value2)

Answer 2

VBA 可以以我们为中心。 特别是由于您的公式在引号中，您应该使用系统列表分隔符，这似乎是;而不是,

对于具有国际意识的版本，请尝试：

sep = Application.International(xlListSeparator)
Cells(k, 4).FormulaLocal = "=MIN(" & Value1 & sep & Value2 & sep & Value3 & ")"