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