Run-time error '1004': AutoFill method of Range class failed

M

murkaboris

Hello:

I wrote a macro that recorded just fine and returned the values I needed but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
 
P

Per Jessen

Hi

Active cell has to be first cell in fill range.

You could active C9 before ActiveCell.Formula.... with this statement if you
always want to fill the same range:

Range("C9").Select

Hopes this helps.
 
M

murkaboris

Hello Per:

I'm not sure I understand, sorry I'm a bit new to writing macros.
Is there any way you could adjust the macro section per your note?
Where would I insert the ActiveCell C9?

Here is the section that's causing the issue....this is the beginning of my
macro...:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
ActiveCell.Select
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

thank you.
Monika

Per Jessen said:
Hi

Active cell has to be first cell in fill range.

You could active C9 before ActiveCell.Formula.... with this statement if you
always want to fill the same range:

Range("C9").Select

Hopes this helps.

---
Per

murkaboris said:
Hello:

I wrote a macro that recorded just fine and returned the values I needed
but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders
OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
Click to expand...
Click to expand...
 
D

Dave Peterson

I don't understand what you're doing.

But I'm gonna guess that you want to put a formula in C9:C16 and uses the value
in column A of the same row in that =vlookup() formula.

Your formula points at R3C1 ($A$3) and I'm not sure why anyone would want that
dragged down a range.

with activesheet.range("c9:c16")
.formular1c1 _
= "=VLOOKUP(RC1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
end with

(Untested. Uncompiled. Watch for typos.)

It just plops the formula into all 8 cells in one line.


Hello:

I wrote a macro that recorded just fine and returned the values I needed but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
Click to expand...
 
M

murkaboris

Hi Dave:

The $A$3 is a unigue identified on each worksheet of the workbook. Its an
employee number based on which the date in C9:C16 is populated. The issue is
that when I run the macro as it was written it changes the cell $A$3 to a
vlookup formula that is in the "C9" cell creating a circular reference with
the run-time error.

I'm not understanding where am I supposed to change the code to:
"activesheet.range("c9:c16"). Should it replace the current
"ActiveCell.FormulaR1C1 = etc....

thanks
Monika
 
D

Dave Peterson

Why would you want to fill C9:C16 with the same formula?

Do you really want that value returned 8 times?

Can you add the formula in C9 manually. Then do the same with C10 and C11.

Then post those formulas.

Hi Dave:

The $A$3 is a unigue identified on each worksheet of the workbook. Its an
employee number based on which the date in C9:C16 is populated. The issue is
that when I run the macro as it was written it changes the cell $A$3 to a
vlookup formula that is in the "C9" cell creating a circular reference with
the run-time error.

I'm not understanding where am I supposed to change the code to:
"activesheet.range("c9:c16"). Should it replace the current
"ActiveCell.FormulaR1C1 = etc....

thanks
Monika

murkaboris said:
Hello:

I wrote a macro that recorded just fine and returned the values I needed but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
Click to expand...
Click to expand...
 
M

murkaboris

Hi Dave:

The formula is almost the same the column that it returns changes. It starts
with "2" and then goes down to "3", "4", till it reaches line 16....so the
result is different on each line to correspond to the column "A" heading.

Monika

Dave Peterson said:
Why would you want to fill C9:C16 with the same formula?

Do you really want that value returned 8 times?

Can you add the formula in C9 manually. Then do the same with C10 and C11.

Then post those formulas.

Hi Dave:

The $A$3 is a unigue identified on each worksheet of the workbook. Its an
employee number based on which the date in C9:C16 is populated. The issue is
that when I run the macro as it was written it changes the cell $A$3 to a
vlookup formula that is in the "C9" cell creating a circular reference with
the run-time error.

I'm not understanding where am I supposed to change the code to:
"activesheet.range("c9:c16"). Should it replace the current
"ActiveCell.FormulaR1C1 = etc....

thanks
Monika

murkaboris said:
Hello:

I wrote a macro that recorded just fine and returned the values I needed but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
Click to expand...
Click to expand...
Click to expand...
 
M

murkaboris

Dave:

To add to my previous post. I created the formula on the first line, then
copied it down through line 16 and then returned and changed the returning
column from 2 on the first line, to 3, 4, and so on....
Hope this makes sense....
Thanks
Monika

murkaboris said:
Hi Dave:

The formula is almost the same the column that it returns changes. It starts
with "2" and then goes down to "3", "4", till it reaches line 16....so the
result is different on each line to correspond to the column "A" heading.

Monika

Dave Peterson said:
Why would you want to fill C9:C16 with the same formula?

Do you really want that value returned 8 times?

Can you add the formula in C9 manually. Then do the same with C10 and C11.

Then post those formulas.

Hi Dave:

The $A$3 is a unigue identified on each worksheet of the workbook. Its an
employee number based on which the date in C9:C16 is populated. The issue is
that when I run the macro as it was written it changes the cell $A$3 to a
vlookup formula that is in the "C9" cell creating a circular reference with
the run-time error.

I'm not understanding where am I supposed to change the code to:
"activesheet.range("c9:c16"). Should it replace the current
"ActiveCell.FormulaR1C1 = etc....

thanks
Monika

:

Hello:

I wrote a macro that recorded just fine and returned the values I needed but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Autofill won't work for this.

This will give you the right results, but it may not be a formula you want to
keep:

With ActiveSheet.Range("c9:c16")
.FormulaR1C1 _
= "=VLOOKUP(R3C3,'[ATF master file.xls]Orders OP'!R3C4:R55C15," _
& "Row(rc)-7,FALSE)"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
End With

You'd end up with a formula like:

=VLOOKUP($A9,'[ATF master file.xls]Orders OP'!$D$3:$O$55,ROW(C9)-7,FALSE)

And that may not be dangerous if you move/copy the cell.

I think I'd just loop through the cells and increment the column to bring back:

Dim iRow As Long
Dim myCell As Range

iRow = 2
For Each myCell In ActiveSheet.Range("c9:c16").Cells
myCell.FormulaR1C1 _
= "=VLOOKUP(R3C3,'[ATF master file.xls]Orders OP'!R3C4:R55C15," _
& iRow & ",FALSE)"
iRow = iRow + 1
Next myCell

With ActiveSheet.Range("c9:c16")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
End With




Dave:

To add to my previous post. I created the formula on the first line, then
copied it down through line 16 and then returned and changed the returning
column from 2 on the first line, to 3, 4, and so on....
Hope this makes sense....
Thanks
Monika

murkaboris said:
Hi Dave:

The formula is almost the same the column that it returns changes. It starts
with "2" and then goes down to "3", "4", till it reaches line 16....so the
result is different on each line to correspond to the column "A" heading.

Monika

Dave Peterson said:
Why would you want to fill C9:C16 with the same formula?

Do you really want that value returned 8 times?

Can you add the formula in C9 manually. Then do the same with C10 and C11.

Then post those formulas.


murkaboris wrote:

Hi Dave:

The $A$3 is a unigue identified on each worksheet of the workbook. Its an
employee number based on which the date in C9:C16 is populated. The issue is
that when I run the macro as it was written it changes the cell $A$3 to a
vlookup formula that is in the "C9" cell creating a circular reference with
the run-time error.

I'm not understanding where am I supposed to change the code to:
"activesheet.range("c9:c16"). Should it replace the current
"ActiveCell.FormulaR1C1 = etc....

thanks
Monika

:

Hello:

I wrote a macro that recorded just fine and returned the values I needed but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
Click to expand...
Click to expand...
Click to expand...
 
M

murkaboris

Thank you Dave, I'll try to rewrite it with the corresponding cells.
Monika

Dave Peterson said:
Autofill won't work for this.

This will give you the right results, but it may not be a formula you want to
keep:

With ActiveSheet.Range("c9:c16")
.FormulaR1C1 _
= "=VLOOKUP(R3C3,'[ATF master file.xls]Orders OP'!R3C4:R55C15," _
& "Row(rc)-7,FALSE)"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
End With

You'd end up with a formula like:

=VLOOKUP($A9,'[ATF master file.xls]Orders OP'!$D$3:$O$55,ROW(C9)-7,FALSE)

And that may not be dangerous if you move/copy the cell.

I think I'd just loop through the cells and increment the column to bring back:

Dim iRow As Long
Dim myCell As Range

iRow = 2
For Each myCell In ActiveSheet.Range("c9:c16").Cells
myCell.FormulaR1C1 _
= "=VLOOKUP(R3C3,'[ATF master file.xls]Orders OP'!R3C4:R55C15," _
& iRow & ",FALSE)"
iRow = iRow + 1
Next myCell

With ActiveSheet.Range("c9:c16")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
End With




Dave:

To add to my previous post. I created the formula on the first line, then
copied it down through line 16 and then returned and changed the returning
column from 2 on the first line, to 3, 4, and so on....
Hope this makes sense....
Thanks
Monika

murkaboris said:
Hi Dave:

The formula is almost the same the column that it returns changes. It starts
with "2" and then goes down to "3", "4", till it reaches line 16....so the
result is different on each line to correspond to the column "A" heading.

Monika

:

Why would you want to fill C9:C16 with the same formula?

Do you really want that value returned 8 times?

Can you add the formula in C9 manually. Then do the same with C10 and C11.

Then post those formulas.


murkaboris wrote:

Hi Dave:

The $A$3 is a unigue identified on each worksheet of the workbook. Its an
employee number based on which the date in C9:C16 is populated. The issue is
that when I run the macro as it was written it changes the cell $A$3 to a
vlookup formula that is in the "C9" cell creating a circular reference with
the run-time error.

I'm not understanding where am I supposed to change the code to:
"activesheet.range("c9:c16"). Should it replace the current
"ActiveCell.FormulaR1C1 = etc....

thanks
Monika

:

Hello:

I wrote a macro that recorded just fine and returned the values I needed but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Or try that suggested code????
Thank you Dave, I'll try to rewrite it with the corresponding cells.
Monika

Dave Peterson said:
Autofill won't work for this.

This will give you the right results, but it may not be a formula you want to
keep:

With ActiveSheet.Range("c9:c16")
.FormulaR1C1 _
= "=VLOOKUP(R3C3,'[ATF master file.xls]Orders OP'!R3C4:R55C15," _
& "Row(rc)-7,FALSE)"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
End With

You'd end up with a formula like:

=VLOOKUP($A9,'[ATF master file.xls]Orders OP'!$D$3:$O$55,ROW(C9)-7,FALSE)

And that may not be dangerous if you move/copy the cell.

I think I'd just loop through the cells and increment the column to bring back:

Dim iRow As Long
Dim myCell As Range

iRow = 2
For Each myCell In ActiveSheet.Range("c9:c16").Cells
myCell.FormulaR1C1 _
= "=VLOOKUP(R3C3,'[ATF master file.xls]Orders OP'!R3C4:R55C15," _
& iRow & ",FALSE)"
iRow = iRow + 1
Next myCell

With ActiveSheet.Range("c9:c16")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
End With




Dave:

To add to my previous post. I created the formula on the first line, then
copied it down through line 16 and then returned and changed the returning
column from 2 on the first line, to 3, 4, and so on....
Hope this makes sense....
Thanks
Monika

:

Hi Dave:

The formula is almost the same the column that it returns changes. It starts
with "2" and then goes down to "3", "4", till it reaches line 16....so the
result is different on each line to correspond to the column "A" heading.

Monika

:

Why would you want to fill C9:C16 with the same formula?

Do you really want that value returned 8 times?

Can you add the formula in C9 manually. Then do the same with C10 and C11.

Then post those formulas.


murkaboris wrote:

Hi Dave:

The $A$3 is a unigue identified on each worksheet of the workbook. Its an
employee number based on which the date in C9:C16 is populated. The issue is
that when I run the macro as it was written it changes the cell $A$3 to a
vlookup formula that is in the "C9" cell creating a circular reference with
the run-time error.

I'm not understanding where am I supposed to change the code to:
"activesheet.range("c9:c16"). Should it replace the current
"ActiveCell.FormulaR1C1 = etc....

thanks
Monika

:

Hello:

I wrote a macro that recorded just fine and returned the values I needed but
when I tried to run it on the workbook and loop it for all worksheets I'm
getting the Run-time error '1004'.

Here is the excerpt from the macro:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R3C1,'[ATF master file.xls]Orders OP'!R3C4:R55C15,2,FALSE)"
Selection.AutoFill Destination:=Range("C9:C16"), Type:=xlFillDefault
Range("C9:C16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With

The line starting with "Selection" and ending with Type:=xlFillDefault" is
the one causing the issue.

Any advise? I'm not too advanced with the macro's, can record simple ones
but have a bit of trouble correcting them.
Thank you.

Monika

Monika
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error '1004': AutoFill method of Range class failed 2
Macro: Insert Row above Totals 6
Help with macro 1
Problem with Macro 7
Run Time error 1004 - Pastespecial method of range class failed. 3
Shorten a Macro 7
Hlp! Adv Filter - Non-Contiguous rows don't copy 11
C++ OLE automation - how do I format the borders? 19

Top