运行宏时VBA中出现错误1004 [英] Error 1004 in VBA while running a macro

问题描述

我有一张4张工作簿。我在Sheet 3中添加了一个名为Amt的宏来查找总和。但是在运行宏时，我得到运行时错误1004.当VBA调试它指向 ActiveSheet.Cells（3，col）.Formula == SUM（& rng&）



注意：该值在 D1，E1 中被接受... ..并根据单元格中的值，我根据 D6，E6 ....的输入计算值的总和，并且总计显示在 D3，E3 ....



我尝试了什么：



代码如下。请帮我解决这个问题。

  Dim  lastCol  As  整数 
 lastCol = ActiveSheet.UsedRange.Column  -   1  + ActiveSheet.UsedRange.Columns.Count 
 
  Dim  col  As  整数 
 对于 col =  4    lastCol ' 从D到完成 
  Dim  numRows  As  长 
 numRows = ActiveSheet.Cells（ 1 ，col）.Value2 
 
  Dim  rng  As  字符串 
 如果 numRows>  0  然后 
 rng = ConvertToLetter（col）&   6：& ConvertToLetter（col）&  CStr （numRows +  2 ）
 
 ' 获取D3，E3，F3中的相应总数.....  
 ActiveSheet.Cells（ 3 ，col）.Formula =   = SUM（& rng&  ） 
  End  如果 
 下一步 

解决方案

Quote：

我得到运行时错误1004.当VBA调试时...

在调试器中，打开局部变量窗口并检查这些变量的值。

它会告诉你代码在做什么。

因为你的代码依赖于cel ls值，我们只能在不知道变量值的情况下猜测发生了什么。



[更新]

你使用的函数ConvertToLetter是错误，需要重写，或使用可用的功能。

尝试使用：

单元格（ 1 ， 1 ）。地址（RowAbsolute：= False，ColumnAbsolute：= False）

我强烈建议您阅读我之前问题的答案：我想要一个VBA代码来查找列中的数字总和。 [ ^

I have a workbook with 4 sheets. I have added a macro in Sheet 3 named Amt to find the sum. But while running the macro, I am getting run time error 1004. While VBA debugging it is pointing to ActiveSheet.Cells(3, col).Formula = "=SUM(" & rng & ")"

Note : The value is accepted in D1,E1..... and based on the value in the cell I am calculating the sum of values based on the input from D6,E6....and total is displayed in D3,E3....

What I have tried:

The code is given below. Please help me to resolve this.

Dim lastCol As Integer
    lastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

    Dim col As Integer
    For col = 4 To lastCol  'From D until finished
        Dim numRows As Long
        numRows = ActiveSheet.Cells(1, col).Value2
        
        Dim rng As String
        If numRows > 0 Then
          rng = ConvertToLetter(col) & "6:" & ConvertToLetter(col) & CStr(numRows + 2)
            
            'Get the appropriate total in D3,E3,F3.....
            ActiveSheet.Cells(3, col).Formula = "=SUM(" & rng & ")"
        End If
    Next

解决方案

Quote:

I am getting run time error 1004. While VBA debugging ...

While in debugger, open the 'Local Variables' window and inspect the values of those variables.
It will tell you what your code was doing.
Since your code depend on cells values, we can only guess what is going on without knowing the variables values.

[Update]
The function ConvertToLetter you use is wrong, it need to be rewritten, or use available functions.
Try to use:

Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

I'd strongly recommend to read my answer to your previous question: I want a VBA code to find the sum of numbers in a column.[^]

这篇关于运行宏时VBA中出现错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！