VBA Code help needed

[Frank3923]

I have the following code that I use to insert formulas. I now have a need to skip a row when inserting the formula. I am not sure how to incorporate a loop into the code, so it will be inserted every two rows, instead of the next available row. Appreciate any help or hints on solving.

Sub fill_Down()

Dim my_range As Range

With Worksheets("Money")
Set my_range = .Range("B6:B" & .Range("E65536").End(xlUp).Row)
my_range.Formula = _
"=IF($Q6="YES",$E7,$E6)"
End With

End Sub

 

[JonathanVallee]

Hi,

I guess there's a lot of ways to accomplish this task but I would use for loops...here's a quick example you can implement in your code:

dim i as int

for i = 6 to 65536
range("B" & i).value = "=IF($Q6="YES",$E7,$E6)"
i = i + 2
next i

That way, you will always skip a row.

Hope this helps.

Thanks

 

[Frank3923]

Jonathan - thank you, I will try implementing this into my macro

 

[hiker95]

Frank3923,

If you were to use either of the above formula/code you should get this:

Excel Workbook
	B	E	Q
6	7	6	YES
7		7
8	7	8
9		9	YES
10	7	10	YES
11		11	YES
12	7	12	YES
13		13	YES
14	7	14	YES
15		15	YES
16	7	16	YES
17		17
18	7	18
19
Money

The formula is not autofilled down (it stayes the same).



Sample data before the updated macro:

Excel Workbook
	B	E	Q
6		6	YES
7		7
8		8
9		9	YES
10		10	YES
11		11	YES
12		12	YES
13		13	YES
14		14	YES
15		15	YES
16		16	YES
17		17
18		18
19
Money

After the macro:

Excel Workbook
	B	E	Q
6	7	6	YES
7		7
8	8	8
9		9	YES
10	11	10	YES
11		11	YES
12	13	12	YES
13		13	YES
14	15	14	YES
15		15	YES
16	17	16	YES
17		17
18	18	18
19
Money

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub fill_Down()
' hiker95, 06/17/2011
' http://www.mrexcel.com/forum/showthread.php?t=558084
Dim LR As Long, a As Long
Application.ScreenUpdating = True
With Worksheets("Money")
  LR = .Cells(Rows.Count, "E").End(xlUp).Row
  For a = 6 To LR Step 2
    .Range("B" & a).FormulaR1C1 = "=IF(RC17=""YES"",R[1]C5,RC5)"
  Next a
End With
Application.ScreenUpdating = False
End Sub

Then run the fill_Down macro.

 

[Frank3923]

Thank you Hiker, It works great

 

[hiker95]

Frank,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.