Do..Loop in multi sheets
 

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


--
norika
------------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
View this thread: http://www.excelforum.com/showthread...hreadid=374112

Do..Loop in multi sheets
 

Add the following line
sh.Select
after line 2 in your second sub.

It should be

Sub Sub_total2l()
For Each sh In ActiveWorkbook.Worksheets
sh.Select
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



Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37411

Do..Loop in multi sheets
 

Thank you. It works.
I have a minor question. In my case, vba applies for all sheets i
active workbook. Is it possible to exclude some sheets not applyin
vba?

TIA

norik

--
norik
-----------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...nfo&userid=487
View this thread: http://www.excelforum.com/showthread.php?threadid=37411

Do..Loop in multi sheets
 

Yes. Try the following if statement:

Sub Sub_total2l()
For Each sh In ActiveWorkbook.Worksheets
if sh.Name < "Sheet1" then ' Sheet1 is then excluded
sh.Select
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
end if
Next sh
End Sub


Use or to include more sheets


Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37411

Do..Loop in multi sheets
 

Thank you very much.
Where do I add 'or' ? I try to add 'or' in line 3
if sh.Name < "Sheet1" or "Sheet9" then .... but it failed.

TIA
norik

--
norik
-----------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...nfo&userid=487
View this thread: http://www.excelforum.com/showthread.php?threadid=37411

Do..Loop in multi sheets
 

Logical statements have to be output fully i.e.

sh.Name < "Sheet1" or sh.Name < "Sheet9"


--
Mountain_Dewski
------------------------------------------------------------------------
Mountain_Dewski's Profile: http://www.excelforum.com/member.php...o&userid=19207
View this thread: http://www.excelforum.com/showthread...hreadid=374112

Do..Loop in multi sheets
 

Hi Norika,

in your case you'll have to use an AND

if ((sh.Name < "Sheet1") AND (sh.Name < "Sheet9")) then

you could add more ANDs

Manges

--
mangesh_yada
-----------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047
View this thread: http://www.excelforum.com/showthread.php?threadid=37411

Do..Loop in multi sheets
 

Thank you very much. I learn much more.

norika


--
norika
------------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
View this thread: http://www.excelforum.com/showthread...hreadid=374112