错误 1004 自动填充 [英] Error 1004 AutoFill

问题描述

错误正在被抛出就行了

ThisWorkbook.Sheets("data").Range("AJ2:AM2").AutoFill Destination:=Range("AJ2:AM" & localLastRow)

它以前是合作的，但在我纠正了另一个错误之后，它似乎不想再玩了.源包含在目标中.我只是不确定问题出在哪里.

It was previously cooperating, but after I corrected another error it seems that it doesn't want to play nice anymore. The source is contained within the destination. I am just not sure where the problem is coming from.

任何帮助将不胜感激.我已经在下面发布了整个宏.它最终会被调用到主宏中.

Any help would be very appreciated. I have posted the entire macro below. It will eventually be one that is called into a main macro.

Sub FormulaUpdate()
'
' FormulaUpdate Macro
' Updates Columns AJ through AS
'
Dim localLastRow As Long
Dim sourceLastRow As Long
Dim wbName As String
Dim wbPath As String
Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim thisSheet As Worksheet

Application.ScreenUpdating = False

'sets strings from user's selection of Item Branch Report
wbPath = GetFile("Select Item Branch Report to be Used")
wbName = GetFilenameFromPath(wbPath)

Workbooks.Open(wbPath, ReadOnly:=True).Activate

'sets workseets to be referenced
Set sourceSheet = ActiveWorkbook.Sheets(1)
Set thisSheet = ThisWorkbook.Sheets("data")

'counts rows in selected item branch report for use elsewhere in macro
sourceLastRow = sourceSheet.Range("A" & Rows.Count).End(xlUp).Row

'range for use in vlookup formula, for both system leadtime and order min columns
Set sourceRange = sourceSheet.Range("B1:BG" & sourceLastRow)

'Counts rows in this workbook for use elswhere in macro
localLastRow = thisSheet.Range("A" & Rows.Count).End(xlUp).Row

'uses formulas in cells to autofill the data
thisSheet.Range("AJ2:AM2").AutoFill Destination:=thisSheet.Range("AJ2:AM" & localLastRow)


'loops through each row of both the system lead time, and the order min column, and sets the value from item branch report
For i = 2 To localLastRow

thisSheet.Range("AN" & i).Value = Application.WorksheetFunction.VLookup(thisSheet.Range("C" & i), sourceRange, 53, False)
thisSheet.Range("AP" & i).Value = Application.WorksheetFunction.VLookup(thisSheet.Range("C" & i), sourceRange, 58, False)
Application.StatusBar = "Referencing IBR: " & i & " of " & localLastRow & ": " & Format(i / localLastRow, "0%")

Next i

'uses formulas in cells to autofill the data
thisSheet.Range("AO2").AutoFill Destination:=thisSheet.Range("AO2:AO" & localLastRow)
thisSheet.Range("AQ2:AS2").AutoFill Destination:=thisSheet.Range("AQ2:AS" & localLastRow)

Workbooks(wbName).Close (False)

推荐答案

我上面提到的是错误的解决方案.阅读这个了解为什么依赖Select 和 Activate 方法通常有问题，应始终避免使用.您已经遇到了一个令人沮丧的问题——即您需要跟踪哪个工作表处于活动状态"并不断更新代码以使适当的工作表处于活动状态".这会导致难以导航且执行成本更高的草率代码.

I mention above that is the wrong solution. Read this for background on why relying on Select and Activate methods is usually problematic, and should be avoided at all times. You have already encountered one frustrating problem -- which is that you need to keep track of which sheet is "active" and constantly update the code to have the appropriate sheet "active". This makes for sloppy code that is difficult to navigate, and more expensive to execute.

适当的解决方案是完全限定您的范围，例如:

The appropriate solution would be to fully qualify your range, for example:

ThisWorkbook.Sheets("data").Range("AJ2:AM2").AutoFill Destination:=ThisWorkbook.Sheets("data").Range("AJ2:AM" & localLastRow)

为什么?

因为，正如您所观察到的，非限定范围总是指的是 ActiveSheet.一种解决方案(错误的)是不断使正确的工作表Active.正确的解决方案是完全限定您的范围，尤其是在跨多个工作簿或工作表工作时.

Because, as you observe, an unqualified range always refers to the ActiveSheet. One solution (the wrong one) is to continuously make the right sheet Active. The right solution is to fully qualify your ranges, especially when working across multiple workbooks or worksheets.

这篇关于错误 1004 自动填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！