Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

Just test visibility:

Sub Macro1()
For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = True Then
MsgBox (sh.Name)
' do your counting
End If
Next
End Sub


--
Gary''s Student - gsnu200833


"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

When you use the For Each sh In ActiveWorkbook.Worksheets, it starts with
Sheets(1) by default. If you only want visible sheets, then add a statement
like:

If sh.Visible = True Then
'your counting code here
End if

How would you start at the last sheet using a For ...Each...Next statement?
You would have to Use something like:

For i = ActiveWorkbook.Sheets.Count To 1 Step - 1
'Code
Next

And Why would you start at the last sheet and work forward? My curiosity is
up.

"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

I believe executing this line...

Sheets(1).Activate

will automatically select the first sheet that is visible.

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if
run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into
the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

Thanks GS

"Gary''s Student" wrote:

Just test visibility:

Sub Macro1()
For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = True Then
MsgBox (sh.Name)
' do your counting
End If
Next
End Sub


--
Gary''s Student - gsnu200833


"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

Thanks JLGWhiz

"JLGWhiz" wrote:

When you use the For Each sh In ActiveWorkbook.Worksheets, it starts with
Sheets(1) by default. If you only want visible sheets, then add a statement
like:

If sh.Visible = True Then
'your counting code here
End if

How would you start at the last sheet using a For ...Each...Next statement?
You would have to Use something like:

For i = ActiveWorkbook.Sheets.Count To 1 Step - 1
'Code
Next

And Why would you start at the last sheet and work forward? My curiosity is
up.

"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

Thanks Rick

"Rick Rothstein" wrote:

I believe executing this line...

Sheets(1).Activate

will automatically select the first sheet that is visible.

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if
run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into
the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

Actually, what was happening was that it was only counting the formulas in
the cells from that sheet that I ran it from, over and over. What fixed it was

If Sheets(sh.Name).Cells(countrow, countcol).HasFormula = True Then

instead of
If Cells(countrow, countcol).HasFormula = True Then



"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

By the way, I just looked at your actual code and I think you can do what
you want without so much looping. Give this macro a try...

Sub CountSheetsAndFormulas()
Dim Sh As Worksheet
Dim Formulas
On Error Resume Next
For Each Sh In Worksheets
Formulas = Formulas + Sh.Cells.SpecialCells(xlCellTypeFormulas).Count
Next
MsgBox "SHEETS in this workbook: " & Sheets.Count & vbLf & vbLf & _
"FORMULAS in this workbook: " & Formulas
End Sub

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Actually, what was happening was that it was only counting the formulas in
the cells from that sheet that I ran it from, over and over. What fixed it
was

If Sheets(sh.Name).Cells(countrow, countcol).HasFormula = True Then

instead of
If Cells(countrow, countcol).HasFormula = True Then



"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if
run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into
the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

Hey Rick, that's great, and soooooo much faster. Thankyou very much for the
extra yards. Brett

"Rick Rothstein" wrote:

By the way, I just looked at your actual code and I think you can do what
you want without so much looping. Give this macro a try...

Sub CountSheetsAndFormulas()
Dim Sh As Worksheet
Dim Formulas
On Error Resume Next
For Each Sh In Worksheets
Formulas = Formulas + Sh.Cells.SpecialCells(xlCellTypeFormulas).Count
Next
MsgBox "SHEETS in this workbook: " & Sheets.Count & vbLf & vbLf & _
"FORMULAS in this workbook: " & Formulas
End Sub

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Actually, what was happening was that it was only counting the formulas in
the cells from that sheet that I ran it from, over and over. What fixed it
was

If Sheets(sh.Name).Cells(countrow, countcol).HasFormula = True Then

instead of
If Cells(countrow, countcol).HasFormula = True Then



"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the macro
from the last worksheet visible it gives me a formula count of 0, but if
run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes into
the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) & Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

By the way, I just noticed that I didn't complete the type declaration for
the Formulas (counter) variable. This statement...

Dim Formulas

should have been this instead...

Dim Formulas As Long

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Hey Rick, that's great, and soooooo much faster. Thankyou very much for
the
extra yards. Brett

"Rick Rothstein" wrote:

By the way, I just looked at your actual code and I think you can do what
you want without so much looping. Give this macro a try...

Sub CountSheetsAndFormulas()
Dim Sh As Worksheet
Dim Formulas
On Error Resume Next
For Each Sh In Worksheets
Formulas = Formulas + Sh.Cells.SpecialCells(xlCellTypeFormulas).Count
Next
MsgBox "SHEETS in this workbook: " & Sheets.Count & vbLf & vbLf & _
"FORMULAS in this workbook: " & Formulas
End Sub

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Actually, what was happening was that it was only counting the formulas
in
the cells from that sheet that I ran it from, over and over. What fixed
it
was

If Sheets(sh.Name).Cells(countrow, countcol).HasFormula = True Then

instead of
If Cells(countrow, countcol).HasFormula = True Then



"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the
macro
from the last worksheet visible it gives me a formula count of 0, but
if
run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes
into
the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) &
Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

Well, it doesn't seem to matter as it works bewdiful as is. Brett

"Rick Rothstein" wrote:

By the way, I just noticed that I didn't complete the type declaration for
the Formulas (counter) variable. This statement...

Dim Formulas

should have been this instead...

Dim Formulas As Long

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Hey Rick, that's great, and soooooo much faster. Thankyou very much for
the
extra yards. Brett

"Rick Rothstein" wrote:

By the way, I just looked at your actual code and I think you can do what
you want without so much looping. Give this macro a try...

Sub CountSheetsAndFormulas()
Dim Sh As Worksheet
Dim Formulas
On Error Resume Next
For Each Sh In Worksheets
Formulas = Formulas + Sh.Cells.SpecialCells(xlCellTypeFormulas).Count
Next
MsgBox "SHEETS in this workbook: " & Sheets.Count & vbLf & vbLf & _
"FORMULAS in this workbook: " & Formulas
End Sub

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Actually, what was happening was that it was only counting the formulas
in
the cells from that sheet that I ran it from, over and over. What fixed
it
was

If Sheets(sh.Name).Cells(countrow, countcol).HasFormula = True Then

instead of
If Cells(countrow, countcol).HasFormula = True Then



"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets and
formulas in a given workbook. The problem is that if I have run the
macro
from the last worksheet visible it gives me a formula count of 0, but
if
run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes
into
the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) &
Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

Yes, I know it works without the "As Long" part, but that is because it
defaults to a Variant... I only use Variants when I have to (in large code
and/or especially in loops, they tend to be slow and memory wasters) and
that variable in the code I posted does not need to be a Variant.

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Well, it doesn't seem to matter as it works bewdiful as is. Brett

"Rick Rothstein" wrote:

By the way, I just noticed that I didn't complete the type declaration
for
the Formulas (counter) variable. This statement...

Dim Formulas

should have been this instead...

Dim Formulas As Long

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Hey Rick, that's great, and soooooo much faster. Thankyou very much for
the
extra yards. Brett

"Rick Rothstein" wrote:

By the way, I just looked at your actual code and I think you can do
what
you want without so much looping. Give this macro a try...

Sub CountSheetsAndFormulas()
Dim Sh As Worksheet
Dim Formulas
On Error Resume Next
For Each Sh In Worksheets
Formulas = Formulas +
Sh.Cells.SpecialCells(xlCellTypeFormulas).Count
Next
MsgBox "SHEETS in this workbook: " & Sheets.Count & vbLf & vbLf & _
"FORMULAS in this workbook: " & Formulas
End Sub

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Actually, what was happening was that it was only counting the
formulas
in
the cells from that sheet that I ran it from, over and over. What
fixed
it
was

If Sheets(sh.Name).Cells(countrow, countcol).HasFormula = True Then

instead of
If Cells(countrow, countcol).HasFormula = True Then



"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets
and
formulas in a given workbook. The problem is that if I have run the
macro
from the last worksheet visible it gives me a formula count of 0,
but
if
run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes
into
the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) &
Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub

OK, I see. I'm still wrapping my head around the finer details of
declarations! Thanks again, you've been a great help.Brett

"Rick Rothstein" wrote:

Yes, I know it works without the "As Long" part, but that is because it
defaults to a Variant... I only use Variants when I have to (in large code
and/or especially in loops, they tend to be slow and memory wasters) and
that variable in the code I posted does not need to be a Variant.

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Well, it doesn't seem to matter as it works bewdiful as is. Brett

"Rick Rothstein" wrote:

By the way, I just noticed that I didn't complete the type declaration
for
the Formulas (counter) variable. This statement...

Dim Formulas

should have been this instead...

Dim Formulas As Long

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Hey Rick, that's great, and soooooo much faster. Thankyou very much for
the
extra yards. Brett

"Rick Rothstein" wrote:

By the way, I just looked at your actual code and I think you can do
what
you want without so much looping. Give this macro a try...

Sub CountSheetsAndFormulas()
Dim Sh As Worksheet
Dim Formulas
On Error Resume Next
For Each Sh In Worksheets
Formulas = Formulas +
Sh.Cells.SpecialCells(xlCellTypeFormulas).Count
Next
MsgBox "SHEETS in this workbook: " & Sheets.Count & vbLf & vbLf & _
"FORMULAS in this workbook: " & Formulas
End Sub

--
Rick (MVP - Excel)


"Brettjg" wrote in message
...
Actually, what was happening was that it was only counting the
formulas
in
the cells from that sheet that I ran it from, over and over. What
fixed
it
was

If Sheets(sh.Name).Cells(countrow, countcol).HasFormula = True Then

instead of
If Cells(countrow, countcol).HasFormula = True Then



"Brettjg" wrote:

Hello there, I have some simple code below which counts the sheets
and
formulas in a given workbook. The problem is that if I have run the
macro
from the last worksheet visible it gives me a formula count of 0,
but
if
run
from the first visible sheet it gives the correct count of 28,119.

How can I make it select the first visible worksheet before it goes
into
the
"For" routine please? Regards, Brett


Sub aa_count_formulas()
Dim counter, countrow, countcol, countsheet, sh As Worksheet

countsheet = 0
counter = 0
For Each sh In ActiveWorkbook.Worksheets
countcol = 1
Do While countcol <= 78
countrow = 1
Do While countrow <= 1000
If Cells(countrow, countcol).HasFormula = True Then
counter = counter + 1
End If
countrow = countrow + 1
Loop
countcol = countcol + 1
Loop
countsheet = countsheet + 1
NextMsgBox "SHEETS in this workbook: " & countsheet & Chr(13) &
Chr(13) &
"FORMULAS in this workbook: " & counter
End Sub