AutoFill difficulty

[Jim Berglund]

I'm getting an Error 1004 on the highlighted line of the following code.

With ActiveSheet
.Range("DG5").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count

.Range("DG5").Value = "=VLOOKUP($DH5,$N$3:$AP$9000,29,TRUE)"
Set SourceRange = .Range("DH5")
Set fillRange = .Range(Cells(6, 111), Cells((numberofRows - 5), 111))
SourceRange.AutoFill Destination:=fillRange
End With

Could you please help me with this? Thanks

 

[Myrna Larson]

The highlighting doesn't show. But are you trying to put a formula in DG5 and
copy it down? If so, you should use the Formula property, not Value, i.e.

.Range("DG5").Formula = "=VLOOKUP($DH5,$N$3:$AP$9000,29,TRUE)"

In case it's the AutoFill command that's the problem, if you look at Help for
AutoFill, you'll see the following:

"Destination Required Range object. The cells to be filled. The destination
must include the source range."

You are violating that rule.

 

[Myrna Larson]

PS: If the purpose is to copy the formula down, you probably want the FillDown
method, not AutoFill. Again, look at Help for FillDown if you need it.

 

[Jim Berglund]

Thanks, Myrna. It works, now.

I think the 'help' for AutoFill messed me up. I copied the example - and
didn't know what else to do when it didn't work...

Jim