时间戳

首页 > 解决方案 > 字符串作为 VBA 中单元格的函数

excel - 字符串作为 VBA 中单元格的函数

问题描述

我在 VBA 中遇到问题。我创建了一个脚本来总结每个月的总和（表的最后一行；应该添加每第三列）到每年的总和。为此，我创建了一个带有 sum 函数的字符串。然后我想将字符串粘贴到月表下的单元格中。

每当我按下按钮

错误 1004

弹出。我尽力自己解决它，但我无法解决它。如果我将字符串传递给 msgbox，它可以正常工作，并且我的屏幕上会显示 =sum(...)。

'Defintions
Dim lastCol As Long
Dim lastRow As Long
Dim i As Integer
Dim k As Integer
Dim x As Integer
Dim l As String
Dim Squantitys As String
Dim year As String
Dim startYear As Integer
Dim str As String
startYear = 8

'Get Dimensions of results
lastRow = Sheets("results").Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Sheets("results").Cells(1, Columns.Count).End(xlToLeft).Column

'Get the new table ready
Sheets("results").Cells(lastRow + 7, 1).Value = "year"
Sheets("results").Cells(lastRow + 7, 2).Value = "total Quantitys"

'add total Quantitys
Squantitys = "=sum("

For i = 2 To lastCol Step 36

     If (lastCol - i) >= 36 Then

          For k = i To i + 33 Step 3

               l = Split(Sheets("results").Cells(lastRow, k).Address, "$")(1)

               Squantitys = Squantitys & l & lastRow & ";"

               x = k + 3

          Next

          l = Split(Sheets("results").Cells(lastRow, x).Address, "$")(1)

          Squantitys = Squantitys & l & lastRow & ")"

     Else

          For k = i To lastCol - 3 Step 3

               l = Split(Sheets("results").Cells(lastRow, k).Address, "$")(1)

               Squantitys = Squantitys & l & lastRow & ";"

               x = k + 3

          Next

          l = Split(Sheets("results").Cells(lastRow, x).Address, "$")(1)

          Squantitys = Squantitys & l & lastRow & ")"

     End If



     'output

     year = Sheets("results").Cells(1, i).Value

     Sheets("results").Cells(lastRow + startYear, 1).Value = year

     'MsgBox Squantitys --- this works fine
     Sheets("results").Cells(lastRow + startYear, 2).Value = Squantitys

     'reset for next loop

     startYear = startYear + 1
     Squantitys = "=sum("

Next

标签： excelvbaruntime-error

解决方案

Squantitys = Squantitys & l & lastRow & ";"

这里改 “;” 到“，”。在 VBA 中仅接受美国格式，不接受本地格式

推荐阅读