时间戳

首页 > 解决方案 > 向其他工作表添加单元格引用的公式的 VBA 代码出现问题

excel - 向其他工作表添加单元格引用的公式的 VBA 代码出现问题

问题描述

当单击用户窗体按钮时，以下代码运行，代码的最后三行出现错误，与Locations变量有关。

代码必须使用用户输入的名称创建一个新工作表，并在“位置”工作表中添加一行数据，其中包含新位置的名称、新创建的工作表中的费用总和以及每个人的费用总和新创建的工作表

Private Sub CommandButton1_Click()

Set Locations = ThisWorkbook.Sheets("Locations")

Dim LastRow As Long
Dim Location As String

Location = TextBox1().Value

If Len(Trim(Location)) = 0 Then
    MsgBox "Please enter a location"
Else
    Dim ws As Worksheet
    With ThisWorkbook
    Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    ws.Name = Location
    Range("A1").Value = "Type"
    Range("B1").Value = "Paid By"
    Range("C1").Value = "Amount"
    End With

    LastRow = Worksheets("Locations").Range("A" & Rows.Count).End(xlUp).Row + 1

    Worksheets("Locations").Range("A" & LastRow).Value = Location
    Worksheets("Locations").Range("B" & LastRow).Formula = "=SUM(" & Location & "!C:C)"
    Worksheets("Locations").Range("C" & LastRow).Formula = "=SUMIF(" & Location & "!B:B;Locations!C2;" & Location & "!C:C)"
    Worksheets("Locations").Range("D" & LastRow).Formula = "=SUMIF(" & Location & "!$B:$B;Locations!D2;" & Location & "!$C:$C)"

End If

End Sub

标签： excelvba

解决方案

  • 如果您的工作表名称有可能有空格，那么您需要在公式中引用它们
  • 在 VBA 中创建公式时，列表分隔符始终是逗号（除非您在可以使用本地列表分隔符时使用 FormulaLocal，但由于各种原因，这不是一个好主意）

未经测试：

Private Sub CommandButton1_Click()

    Dim LastRow As Long
    Dim Location As String
    Dim ws As Worksheet, Locations As Worksheet

    Set Locations = ThisWorkbook.Sheets("Locations")
    Location = Trim(TextBox1.Value)

    If Len(Location) = 0 Then
        MsgBox "Please enter a location"
    Else

        With ThisWorkbook
            Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        End With
        ws.Name = Location
        ws.Range("A1").Value = "Type"
        ws.Range("B1").Value = "Paid By"
        ws.Range("C1").Value = "Amount"


        With Locations.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow
            .Cells(1).Value = Location
            .Cells(2).Formula = "=SUM('" & Location & "'!C:C)"
            .Cells(3).Formula = "=SUMIF('" & Location & "'!B:B,Locations!C2,'" & Location & "'!C:C)"
            .Cells(4).Formula = "=SUMIF('" & Location & "'!$B:$B,Locations!D2,'" & Location & "'!$C:$C)"
        End With
    End If

End Sub

推荐阅读