带有运行时错误的Excel VBA宏'1004'

3 回答

• 0

  尝试清除正在复制高级过滤器值的目标单元格的内容，看它是否有效 .

  Sub stock_exercise()
  Dim RowNbr As Long
  Dim uniqueticker As Long
  Dim totalvolume As String
  Dim r As Long
  Dim wksOne As Worksheet
  
  Set wksOne = ThisWorkbook.Sheets("A") 'determine row count
  RowNbr = wksOne.Range("A1", wksOne.Range("A1").End(xlDown)).Rows.Count
  
  'clear contents of destination cell before running the code
  wksOne.Range("J1").CurrentRegion.ClearContents
  
  'copy and past unique ticker from column a to column j
  wksOne.Range("a1:a" & RowNbr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wksOne.Range("J1"), Unique:=True
  
  wksOne.Range("J1").Value = "ticker"
  uniqueticker = wksOne.Range("j1", Range("j1").End(xlDown)).Rows.Count
  
  'sumif of each ticker
  For r = 2 To uniqueticker
      totalvolume = Application.SumIf(wksOne.Range("a1:a" & RowNbr), wksOne.Cells(r, 10), wksOne.Range("g1:g" & RowNbr))
      wksOne.Cells(r, 11).Value = totalvolume
  Next r
  End Sub
  

  回复于 2024-05-03T09:08:36+08:00
• 0

  失败时，选择Debug并查看"Immediate Window" . 在即时窗口中，键入 ?RowNbr 并按Enter键 . 您将看到RowNbr的值...它可能为null（导致错误） . 如果是，您可以在立即窗口中备份代码，例如，每一行依次粘贴？在前 . 您可以检查范围是否符合预期，工作表甚至工作簿，例如 ?sheet1.name 和 ?thisworkbook.name .

  您还可以粘贴每行的细分，以检查其值是否符合预期，例如 ?sheet1.Range("a1:a" & RowNbr).address

  回复于 2024-05-03T09:08:36+08:00
• 0

  我做了一些代码清理和下面的代码 . 我只测试了空标准范围，这意味着列K（代码中的第11列）填充了零值，因此如果您使用此代码重新显示 run-time error 1004 ，则表明问题出在此处 . 在这种情况下，请确保SumIf函数的条件范围中的所有值都有效 .

  Sub stock_exercise()
      Dim r As Long, RowNbr As Long
      Dim uniqueticker As Long
      Dim rng As Range
      Dim sheet1 As Worksheet
  
      Application.ScreenUpdating = False
  
      Set sheet1 = ThisWorkbook.Sheets("A")
  
      With sheet1
  
          'determine row count
          RowNbr = .Range("A1", .Range("A1").End(xlDown)).Rows.Count
  
          Set rng = .Range("a1:a" & RowNbr)
  
          'copy and paste unique ticker from column a to column j
          rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("J2"), Unique:=True
  
          .Range("J1").Value = "ticker"
          uniqueticker = .Range("j1", Range("j1").End(xlDown)).Rows.Count - 1 ' minus one, since we insert from row two
  
          'sumif of each ticker
          For r = 2 To uniqueticker + 1
  
              ' insert total volume
              .Cells(r, 11).Value = Application.SumIf(rng, .Cells(r, 10), .Range("g1:g" & RowNbr))
          Next r
  
      End With
  
      Application.ScreenUpdating = True
  
  End Sub
  

  回复于 2024-05-03T09:08:36+08:00