How to overcome error:  1004 - autofill method of range class failed using Excel VBA?

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
How to overcome error:  1004 - autofill method of range class failed using Excel VBA?
Please note following code and values of variables are remarked below:

How do I change it to accept what I'm trying to accomplish?

Basically the range:  C39 contains the formula ->  =SUM(C7:C38) and want to copy that across Range D39:N39 where it can total those different columns in similar fashion.

Dim selection1 As Range
  Dim selection2 As Range

  'sColLetterStart = "C"
  Set selection1 = m_xlSheet.Range(sColLetterStart & "39")
  'sTotalCompleteRange = "D39:N39"
  Set selection2 = m_xlSheet.Range(sTotalCompleteRange)
  selection1.AutoFill Destination:=selection2 ', Type:=xlFillDefault
  Set selection2 = Nothing
  Set selection1 = Nothing
  

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
The autofill destination range must include the cell being copied to the destination range so either you set the selection2 range like this
Set selection2 = Union(selection1, m_xlSheet.Range(sTotalCompleteRange))
selection1.AutoFill Destination:=selection2 ', Type:=xlFillDefault

Open in new window

OR

Set selection2 = m_xlSheet.Range(sTotalCompleteRange)
selection1.AutoFill Destination:=Union(selection1, selection2) ', Type:=xlFillDefault

Open in new window

Author

Commented:
I get a Run-time error '1004':  method union of object '_global' failed on any of code line item that contains the word Union.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You commented out few lines in the sample code you provided in your first post.
Did you uncomment those line before testing the code?
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Author

Commented:
Union doesn't work for me at all in Excel VBA.  The uncomment is not relevant to the solution.

Author

Commented:
Here's a sample of a file I"m trying to do.  Please show me how to copy the formula in cell c15 to the entire row on 15 for their perspective totals
sample-excel-pull-list.xlsm
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
The sample file doesn't contain the code you posted in the description.
What exactly is the issue you are having?

Author

Commented:
"Please show me how to copy the formula in cell c15 to the entire row on 15 for their perspective totals" using Excel VBA based on the variables provided.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You could simply try this...
Dim FormulaCell     As Range
Dim destRange       As Range

Set FormulaCell = Range("C15")
Set destRange = Range("C15:N15")       'destRange contains the cell being copied i.e. C15

FormulaCell.AutoFill destRange, xlFillDefault

Open in new window

The above code will autofill formula in cell C15 to the range C15:N15. Note how the destination range includes the cell with formula to copy i.e. C15.

Or you could try this...
Dim FormulaCell As Range
Dim destRange   As Range

Set FormulaCell = Range("C15")
Set destRange = Range("D15:N15")    'destRange doesn't contain the cell being copied i.e. C15

FormulaCell.Copy
destRange.PasteSpecial xlPasteFormulas
Application.CutCopyMode = 0

Open in new window

Author

Commented:
Yea this time I don't get the error but it still refuses to copy the formula throughout the 2nd range.

Did you run the sample with the above code?
I still have to transfer this to another project.

Everyone keeps giving me the set value back without the variable inside it.
I need it this way:

Dim sFirstCol as String
sFirstCol = "C15"
Set FormulaCell = Range(sFirstCol)
Dim sSecCol as String
sSecCol = "D15:N15"
Set destRange = Range(sSecCol)
FormulaCell.Copy
destRange.PasteSpecial xlPasteFormulas
Application.CutCopyMode = 0

Open in new window


When its' written above it doesn't work for me.
I can't declare D15:N15 cause those values will change.
Consultant and developer
Commented:
You don't really need to do any "copy and paste" stuff, or autofill.

For instance:
Sub copyFormulae()
Dim rSource As Range, rDest As Range

'set source range
Set rSource = ActiveSheet.Cells(2, 4)

'set destination range
Set rDest = rSource.Resize(4, 2).Offset(0, 2)
'set formula
rDest.Formula = rSource.Formula


End Sub

Open in new window


will also achieve exactly what you need. It sets a source range, then a destination range that can be any size or shape, and any offset from the source. The final command populates the destination with your source formula.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial