In order to try and reduce the size of an analytical
workbook I have,I'm trying to write some VBA to copy and
paste VLOOKUP equations dependent upon the number of
cells in another column. For example, I have a column of
for example, 5000 numbers(there could be as many as
30,000 numbers) in column E, starting in E2. The
equations to be copied run in row 2, columns F through M.
The lookup array exists from A2 to D1000.
The task is this:
1) Determine the base row of data in column E.
2) Copy the equations from F2 through M2
3) Paste the equations from F3 to the base row of column
M, as determined from Task 1.

I've been able to get bits and pieces to work, but when I
jam them together, I'm lost, especially in Task #3. Any
help would be greatly appreciated. Thanks!

Stolen from Tom Ogilvy, who must be getting his one hour of sleep now:

dim nrows as integer
nrows = 15
Range("F2").Resize(1, 8).Copy Destination:=Range("f3").Resize(nrows, 8)

You might try to come up with a way to not hardcode the 8, such as with
a range "BaseFormulae" (which is F2 through M2) and use
range("BaseFormulae").columns.Count.

Then you have
Range("BaseFormulae").Copy Destination:=Range("f3").Resize(nrows,
range("BaseFormulae").columns.Count)

But even better, generalize "F3" too. If you define the single cell F3
as "DestRow1" you get

Range("BaseFormulae").Copy Destination:=Range("DestRow1").Resize(nrows,
range("BaseFormulae").columns.Count)

You can sleep better with the final formula, since one day rows or
columns may be inserted or deleted without breaking your code.

There's one more "hardcoded" piece left, though -- nrows. From what
you've seen here, do you know how you could set it dynamically? Take a
stab at it and feel welcome to scream back here. Whoever is awake will
be happy to help you keep moving ahead :)

P.S. Have you given thought to clearing out unused formulas, in case one
day column E was to contract, since rows that your macro produced in the
past would contain formulas that aren't being used?

On Mon 25 Aug 2003 19:02:12 -0700,"dave_d" wrote:

In order to try and reduce the size of an analytical
workbook I have,I'm trying to write some VBA to copy and
paste VLOOKUP equations dependent upon the number of
cells in another column. For example, I have a column of
for example, 5000 numbers(there could be as many as
30,000 numbers) in column E, starting in E2. The
equations to be copied run in row 2, columns F through M.
The lookup array exists from A2 to D1000.
The task is this:
1) Determine the base row of data in column E.
2) Copy the equations from F2 through M2
3) Paste the equations from F3 to the base row of column
M, as determined from Task 1.

I've been able to get bits and pieces to work, but when I
jam them together, I'm lost, especially in Task #3. Any
help would be greatly appreciated. Thanks!