☰

与我的工作表中的正确表格对应的公式，vba。多个工作表中的多个表格

tag-icon • tag-icon microsoft-excel worksheet-function vba
与我的工作表中的正确表格对应的公式，vba。多个工作表中的多个表格

我正在使用 VBA 设置循环盘点表。表格中的公式仅引用表中的第一个表格作为输入值，这让我很头疼。

例如，每张表有 4 个表格，标题表 1 从单元格 A2 开始。.C3=B3*A3表 2，公式为C68=B3*A3。我需要它读取C68=B68*A68。

我刚开始学习 VBA，所以我肯定我遗漏了一些东西。这只是宏的一部分。我的工作表设置为每个月。任何帮助都将不胜感激。

Dim i As Integer
Dim monthName As String
Dim WEEKOF As Integer
Dim DATEVALUE As Date
Dim SPACE As Integer
Dim ws As Worksheet
Dim cell As Range
Dim tableName As String
Dim tableRange As Range
Dim tbl As ListObject
WEEKOF = 1
SPACE = 0
Worksheets(WEEKOF).Activate
Range("A1").Formula = "=DATE(YEAR(TODAY()),MONTH(1),WEEKDAY(3,1))"
DATEVALUE = Range("A1")
For WEEKOF = 1 To 12
    Do Until Month(DATEVALUE) > WEEKOF
        Worksheets(WEEKOF).Activate
        Range("A1").Offset(SPACE, 0).Value = DATEVALUE
        'Adding forumlas and setting up total boxes
        Range("I3:I50").Offset(SPACE, 0).Formula = "=H3*G3"
        Range("L3:L50").Offset(SPACE, 0).Formula = "=IF(ABS(O3)>=10,""NEEDS RECOUNT"",""N/A"")"
        Range("N3:N50").Offset(SPACE, 0).Formula = "=E3-D3"
        Range("O3:O50").Offset(SPACE, 0).Formula = "=F3-D3"
        Range("P3:P50").Offset(SPACE, 0).Formula = "=F3/D3"
        Range("R3:R50").Offset(SPACE, 0).Formula = "=ABS(I3)"
        Range("S3:S50").Offset(SPACE, 0).Formula = "=ABS(O3)"
        Range("A52").Offset(SPACE, 0).Value = "Totals"
        Range("A52").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A53").Offset(SPACE, 0).Value = "Adjustment Cost Total"
        Range("A53").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A54").Offset(SPACE, 0).Formula = "=SUM(I3:I50)"
        Range("A55").Offset(SPACE, 0).Value = "Gross Discrepancy Cost"
        Range("A55").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A56").Offset(SPACE, 0).Formula = "=SUM(R3:R50)"
        Range("A57").Offset(SPACE, 0).Value = "Total on Hand Counted Parts"
        Range("A57").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A58").Offset(SPACE, 0).Formula = "=SUM(F3:F50)"
        Range("A59").Offset(SPACE, 0).Value = "Gross Discrepancy Count"
        Range("A59").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A60").Offset(SPACE, 0).Formula = "=SUM(S3:S50)"
        Range("A61").Offset(SPACE, 0).Value = "Number of Lines"
        Range("A61").Offset(SPACE, 0).Font.FontStyle = "Bold"
        Range("A62").Offset(SPACE, 0).Formula = "=COUNTA(UNIQUE(FILTER(A3:A50,A3:A50<>"""")))"
        SPACE = SPACE + 65
        DATEVALUE = DateAdd("D", 7, DATEVALUE)
        If Year(DATEVALUE) > Year(Date) Then
        Exit Do
        End If
    Loop
SPACE = 0
If Year(DATEVALUE) > Year(Date) Then
Exit For
End If
Next WEEKOF

答案1

一个好主意是将公式定义样式更改为FormulaR1C1如下所示：

Sub Test()
   Dim i As Integer
   Dim monthName As String
   Dim WEEKOF As Integer
   Dim DATEVALUE As Date
   Dim SPACE As Integer
   Dim ws As Worksheet
   Dim cell As Range
   Dim tableName As String
   Dim tableRange As Range
   Dim tbl As ListObject
   WEEKOF = 1
   SPACE = 0
   Worksheets(WEEKOF).Activate
   Range("A1").Formula = "=DATE(YEAR(TODAY()),MONTH(1),WEEKDAY(3,1))"
   DATEVALUE = Range("A1")
   For WEEKOF = 1 To 12
      Do Until Month(DATEVALUE) > WEEKOF
         Worksheets(WEEKOF).Activate
         Range("A1").Offset(SPACE, 0).Value = DATEVALUE
        'Adding forumlas and setting up total boxes
         Range("I3:I50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-1]*RC[-2]"
         Range("L3:L50").Offset(SPACE, 0).FormulaR1C1 = "=IF(ABS(RC[3])>=10,""NEEDS RECOUNT"",""N/A"")"
         Range("N3:N50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-9]-RC[-10]"
         Range("O3:O50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-9]-RC[-11]"
         Range("P3:P50").Offset(SPACE, 0).FormulaR1C1 = "=RC[-10]/RC[-12]"
         Range("R3:R50").Offset(SPACE, 0).FormulaR1C1 = "=ABS(RC[-9])"
         Range("S3:S50").Offset(SPACE, 0).FormulaR1C1 = "=ABS(RC[-4])"
         Range("A52").Offset(SPACE, 0).Value = "Totals"
         Range("A52").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A53").Offset(SPACE, 0).Value = "Adjustment Cost Total"
         Range("A53").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A54").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-51]C[8]:R[-4]C[8])"
         Range("A55").Offset(SPACE, 0).Value = "Gross Discrepancy Cost"
         Range("A55").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A56").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-53]C[17]:R[-6]C[17])"
         Range("A57").Offset(SPACE, 0).Value = "Total on Hand Counted Parts"
         Range("A57").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A58").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-55]C[5]:R[-8]C[5])"
         Range("A59").Offset(SPACE, 0).Value = "Gross Discrepancy Count"
         Range("A59").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A60").Offset(SPACE, 0).FormulaR1C1 = "=SUM(R[-57]C[18]:R[-10]C[18])"
         Range("A61").Offset(SPACE, 0).Value = "Number of Lines"
         Range("A61").Offset(SPACE, 0).Font.FontStyle = "Bold"
         Range("A62").Offset(SPACE, 0).FormulaR1C1 = "=COUNTA(UNIQUE(FILTER(R[-59]C:R[-12]C,R[-59]C:R[-12]C<>"""")))"
         SPACE = SPACE + 65
         DATEVALUE = DateAdd("D", 7, DATEVALUE)
         If Year(DATEVALUE) > Year(Date) Then Exit Do
      Loop
      SPACE = 0
      If Year(DATEVALUE) > Year(Date) Then Exit For
   Next WEEKOF
End Sub

相关内容