Autofill series based on values in a column using Macro

[boolok2011]

I would like to use macro to autofill series in column B and autofill in column C based on the following conditions:

if the row in column A has a value 3 or 4 or 5, then autofill column B in series and determines the value at the end row, then autofill column C with that value in that row. For example:

ColumnA ColumnB ColumnC
-----------------------------------------
1 1(autofill startpt) 6
1 2 6
1 3 6
1 4 6
1 5 6
3 6(autofill endpt) 6
0 1(autofill start pt) 4
1 2 4
2 3 4
3 4(autofill endpt) 4
2 1(autofill start pt) 5
1 2 5
2 3 5
0 4 5
4 5(autofill endpt) 5

thx ahead.

 

[boolok2011]

2007

 

[boolok2011]

Excel 2007.

 

[jim may]

Your code looks OK - I dont see the error, but try changing the line below to

Allow for xl2007's 4 char extentions

strFile = Dir(strPath & "*.xls*") 'in casde you have any .xlsx files

otherwise - try stepping thru the code (pressing F8) to see exactly where the error is occuring

 

[boolok2011]

Jim, I have another autofill condition that are quite complicated and I would deeply appreciate if you can give some input. Here are the conditions:

Procedures:

1. The column AW starting at row 5 (AW5) can consist of values: 0,1,3,4,5,2f,2h,-2f,2h

2. Go down to seek value 4 or 5. This sets the range for analysis.

3. Within the above range, fill in the char I, M, or F

Case 1: Does not have any 3 between the range(s), then autofill from BJ5 with char "I"

colAW colBJ
row5 2h I
row6 1 I
row7 1 I
row8 1 I
row9 5 I
------------------------------
row10 0 I
row11 1 I
row12 -2h I
row13 4 I

Case 2:
Only have one 3 within the range(s) bounded by 4 or 5, then autofill the 1st range with I, and the last range with F

colAW colBJ
row5 -2f I
row6 1 I
row7 1 I
row8 1 I
row9 1 I
row10 3 I
---------------------
row11 1 F
row12 2h F
row13 -2f F
row14 5 F


Case 3: Have two 3s within the range(s) bounded by 4 or 5, then autofill the 1st with I, 2nd set with M, and the last set with F

colAW colBJ
row5 0 I
row6 1 I
row7 3 I
----------------------
row8 1 M
row9 -2f M
row10 0 M
row11 3 M
----------------------
row12 2h F
row13 -2f F
row14 5 F

Case 4:
Have three or more 3s within the range(s) bounded by 4 or 5, then autofill the 1st with I, 2nd set with M, 3rd set with M, n set with M, and the last set with F

colAW colBJ
row5 0 I
row6 1 I
row7 3 I
----------------------
row8 1 M
row9 -2f M
row10 0 M
row11 3 M
----------------------
row12 3 M
----------------------
row13 1 M
row14 -2h M
row15 1 M
row16 3 M
----------------------
row12 2h F
row13 -2f F
row14 4 F

 

[boolok2011]

Anyone can give me some direction/idea. This is the last column I need to work on. Thx ahead.