Paste Down

[STEVEB]

Hi,

I am trying to paste my VLOOKUP formula down & am getting the following
error:

Run time error 438 - Object dosent support this property or method.

The VLookup works in the first cell (B2) but is not copied down to to
the rest of the cells in column B.

This is my code......

Worksheets("Group 40").Activate

Set CurrentCell = Range("A2")
Do While Not IsEmpty(CurrentCell)
Set CurrentCell = CurrentCell.Offset(0, 1)
Set NextCell = CurrentCell.Offset(1, 0)

Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1], '[Reportable
Accounts.xls]Sheet1'!R1C1:R1147C2,1,FALSE)"
CurrentCell.Copy
Selection.Paste
Application.CutCopyMode = False
Set CurrentCell = NextCell

Loop


I would like to copy the VLOOKUP Formula to the rest of the cells in
column B does anyone have any suggestions?

Thanks

 

[Don Guillett]

I don't use r1c1 but
Sub copyformula()
For Each c In Selection
c.Formula = "=vlookup(a1,$b$1:$c$5,2,false)"
c.Formula = c.Value 'will leave values only
Next
End Sub

 

[Doug Glancy]

Steve,

Since you never select a new cell after B2, "Selection.Paste" will always
paste into B2.

If you want to put the formula into every cell in column B, this seems to
work. However I notice that the formula seems to be returning the value
that is being looked up, so I'm not sure what it does. It certainly takes a
while to recalculate all those lookups (at least on my old PII).

Sub test()
Worksheets("Group 40").Columns(2).FormulaR1C1 = _
"=VLOOKUP(RC[-1], '[Reportable Accounts.xls]Sheet1'!R1C1:R1147C2,1,FALSE)"
End Sub

hth,

Doug

 

[Tom Ogilvy]

See my answer to your original question - the code I provided will be many
times faster than your inefficient loop. And it works.


Sub tester3()
Dim rng As Range
With Worksheets("Sheet 1") ' <= Want a space in the name?
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rng.Offset(0, 1).Formula = _
"=VLOOKUP(A2," & _
"'[FileName.xls]Sheet1'!" & _
"$A$2:$B$1147,2,FALSE)"

rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
End Sub


Might want the 3rd argument of Vlookup to be a 2 rather than a 1.

 

[STEVEB]

Hi Tom,

This formula worked great? Is there a way to autmatically delete all
"#N/A" cells that are returned as a result of the VLOOKUP?

Thanks for your help!