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

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" skrev i meddelelsen
...
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

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" wrote:

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" skrev i meddelelsen
...
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

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.



murkaboris wrote:

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

--

Dave Peterson

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" wrote:

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

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

"murkaboris" wrote:

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

--

Dave Peterson

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" wrote:

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

"murkaboris" wrote:

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

--

Dave Peterson

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" wrote:

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" wrote:

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

"murkaboris" wrote:

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

--

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





murkaboris wrote:

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" wrote:

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" wrote:

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

"murkaboris" wrote:

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

--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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





murkaboris wrote:

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" wrote:

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" wrote:

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

"murkaboris" wrote:

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

--

Dave Peterson


--

Dave Peterson

Or try that suggested code????

murkaboris wrote:

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

"Dave Peterson" wrote:

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





murkaboris wrote:

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" wrote:

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" wrote:

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

"murkaboris" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson