N
norika
Hi all,
I am writing a do loop vba, it is fine for applying one sheet. Now I
want to write vba to automate the do loop program for several sheets.
How can I do it?
The following program is only appling to one sheet:
Sub Sub_total()
Range("J2").Select
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>R[1]C[-8],SUMIF(R2C[-8]:RC[-8],RC[-8],R2C[-1]:RC[-1]),"""")"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
I tried the follwoing code, but it failed.
Sub Sub_total2l()
For Each sh In ActiveWorkbook.Worksheets
With sh
Range("J2").Select
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>R[1]C[-8],SUMIF(R2C[-8]:RC[-8],RC[-8],R2C[-1]:RC[-1]),"""")"
ActiveCell.Offset(1, 0).Select
Loop
End With
Next sh
End Sub
norika
I am writing a do loop vba, it is fine for applying one sheet. Now I
want to write vba to automate the do loop program for several sheets.
How can I do it?
The following program is only appling to one sheet:
Sub Sub_total()
Range("J2").Select
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>R[1]C[-8],SUMIF(R2C[-8]:RC[-8],RC[-8],R2C[-1]:RC[-1]),"""")"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
I tried the follwoing code, but it failed.
Sub Sub_total2l()
For Each sh In ActiveWorkbook.Worksheets
With sh
Range("J2").Select
Do While IsEmpty(ActiveCell.Offset(0, -6)) = False
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]<>R[1]C[-8],SUMIF(R2C[-8]:RC[-8],RC[-8],R2C[-1]:RC[-1]),"""")"
ActiveCell.Offset(1, 0).Select
Loop
End With
Next sh
End Sub
norika