vba - 在 R1C1 公式中使用时,带小数的变量会转换为 2 位数字
问题描述
所以我的问题很简单。在我的 VBA 代码中,我从 3 个单元格中检索 3 个值。在下面的示例中,Value1 = 110,5;Value2=100;Value3=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)
你有解决这个问题的办法吗?
在此先感谢您的帮助!
解决方案
Your comma-as-decimal-placeholder is conflicting with the default EN-US list separator. Use .FormulaLocal and write the formula as it would appear to you on the worksheet.
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) & ")"
Looking at the way you're using k, it could easily be inferred that you are running a loop like for k=2 to lastRow
. If that is the case, then write all of the formulas at once.
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
If you are hard-coding values into the worksheet formula, you might as well just write the result value in.
Cells(k, 4) = application.min(Worksheets(Countryname).Cells(k, 5).value2, _
Worksheets(Countryname).Cells(k, 14).value2, _
Worksheets(Countryname).Cells(k, 23).value2)
推荐阅读
- python-3.x - 关系解析
- sqlite - react-native - 如何在现有应用程序的表中添加列?
- javascript - 如何使用脚本 v2019 水平分隔组中的 Photoshop 图层
- python - 将 N 个列表按索引合并在一起
- postman - 使用邮递员从保存在变量中的值中删除双引号
- kubernetes - Kubernetes CRD 可以用作内置资源的捆绑包吗?
- python - 您如何通过按日期时间索引的数据框进行集成?
- django - Django:ContentFile 问题:字符串内容未完全下载
- python - 如果其值为无,则隐藏表单上的只读字段
- c++ - c++字符串使用assign函数和直接使用'='改变值的区别