Pasting formula in every nth row

[neil]

hi,

I have a formula that needs to be pasted into every 5th row, only if there
is no existing formula in that target row.

I have about 30000 rows of data & 10 such spreadsheets. To hasten this task,
I wrote this code, but it is missing out pasting the formula on some rows
etc...

Sub Macro2()

Dim Count As Long


Range("B5:M5").Copy

For Count = 0 To 70 Step 5
If Range("B5").Offset(Count, 0).HasFormula Then
Count = Count + 5
Else
Range("B5").Offset(Count, 0).PasteSpecial
Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Count = Count + 5
End If
Next Count

End Sub


TIA

Neil

 

[Otto Moehrbach]

Your For statement is incrementing Count by 5. Then your code increments it
again with your Count=Count+5. That's 10!!! Don't increment Count inside
the For loop. Also, change the IF statement:
If Range("B5").Offset(Count, 0).HasFormula Then
Else
End If
to
If Not Range("B5").Offset(Count, 0).HasFormula Then
'Do the copy/paste
End If
HTH Otto

 

[Sam Wilson]

Hi,

When using a for... next statement you don't have to manually increment your
variable. Try these:

For count = 0 to 70 step 5
msgbox count
count = count + 5
next count

For count = 0 to 70 step 5
msgbox count
next count

You'll see that the first returns 0,10,20,30... rather than 0,5,10... like
the second one does. Delete your count = count + 5 statements

Sam

 

[neil]

Thanks for pointing me in the right direction Sam!

Neil