How to copy the same formulas in a spreadsheet -Macro?

[Guest]

I have a spreadsheet that has 34 lines pers employee. I have a column called
payfield and payfield amount. I want to copy the formula down. Payfield has
Pay field number 1-37 as listed below, but only 34 lines. The formula will
is in the Column L as shown below. After PAY FIELD NUMBER then it starts
over again. Hope this is clear.

COLUMN I COLUMN L
STARTING ROW 2 STARTING ROW 2

Pay Field-Number Payfield Amount
1 Has a VLOOKUP FORMULA
2 BLANK
3 BLANK
4 BLANK
5 BLANK
6 BLANK
7 BLANK
8 BLANK
9 BLANK
10 BLANK
11 BLANK
12 BLANK
13 BLANK
14 BLANK
15 BLANK
16 BLANK
17 BLANK
18 BLANK
19 BLANK
20 BLANK
22 HAS A VLOOKUP FORMULA
23 HAS A VLOOKUP FORMULA
24 HAS A VLOOKUP FORMULA
25 HAS A VLOOKUP FORMULA
26 HAS A VLOOKUP FORMULA
27 HAS A VLOOKUP FORMULA
28 HAS A VLOOKUP FORMULA
29 HAS A VLOOKUP FORMULA
30 HAS A VLOOKUP FORMULA
31 HAS A VLOOKUP FORMULA
32 HAS A VLOOKUP FORMULA
33 HAS A VLOOKUP FORMULA
36 HAS A VLOOKUP FORMULA
37 HAS A VLOOKUP FORMULA


I am looking for an easy way to copy the formula all the way down without
going to the fields one by one and copy the VLOOKUP FORMULA and of course
leave the lines blank that should be. Need to finish this spreadsheet
tonight. I can e-mail the spreadsheet if that will make it clearer.

Thanks in Advance any help or solution. Need to import Beg Balances by
tomorow morning - early am like 3 am at the latest.

 

[bony_tony]

If you enter this formula;
=IF(AND(I3>1,I3<21),"",VLOOKUP(your lookup parameters))
into I3, and copy it down, it will display a blank cell if the pay-
field number is between 2 & 20.

Cheers
Tony

 

[Guest]

This does not work for the fact that if I use Fill down then I have to go
back and change the column-index number for each payfield. When I use Fill
down to copy the formula it doesn't change the column index number. I can
adjust the formula for 1-37 for each column index and then copy and paste a
section but now I have to do this for each employee, which I was doing
before. I was looking for a easy way to copy the VLOOKUP FORMULA and remeber
the column index number does change.

 

[Gord Dibben]

klafert

In your vlookup formula use the ROW() function to return the column index
number.

Example only.........change ranges and starting ROW() to suit.

=VLOOKUP(G1,$C$1:$M$50,ROW(),FALSE)

Combine with ISBLANK or IF formula that returns "" if cell blank.

BTW: if you had posted an actual formula showing the table range it would help
to tailor something to suit.

You say you have 37 column indexes so you must have at least 37 columns. What
is the table range and lookup value.

I can't tell from your description.


Gord Dibben MS Excel MVP