Macro Autofill Question

[Andrew Biafore]

Hi, I am writing a Macro in (Excel 2003) and I am having trouble with
the Autofill command. First of all I am just copying my movements, not
writing them in VB.

The problem that I am having is that I need the Macro to use the
Autofill command that I am giving it. The reason is I am writing a
Macro for work that will be plugging in data that could be 100 rows, or
2000 rows. I am using the VLOOKUP to pull data on to the sheet and than
Autofill to populate all the rows with the VLOOKUP data.

It works when I run the Macro, but if there were 121 rows of data when I
wrote the Macro, it will only go to 121 rows when I run the Macro.
Which is the problem.

Is there any way to get around this issue with out having to write the
Macro in VB?

Thanks,
Andrew

 

[Bill Renaud]

Give us a small sample of data (say 10 cells of data). Tell us where is the
data (what cells), and where are the cells with the VLOOKUP function?

You post is a little confusing. At the top, you say:
<<...I am writing a Macro in (Excel 2003) ...>>

....then at the bottom you say:
<<Is there any way to get around this issue with out having to write the
Macro in VB?>>

In general, you don't need to use the AutoFill function when programming a
formula into a range of cells using VBA. You simply use the Formula
property with the range of cells you want to fill.

Say you have data in Range("A1:A10"), and you are going to fill in column B
next to the data in column A. You could do this simply by:

Dim rngColB as Range

Set rngColB = ActiveSheet.Range("B1:B10")

rngColB.Formula = "=A1 + 5"

In your case, you would have to use variables to get the number of actual
cells in column A, so that the Set statement would be correct.