Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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

Finding the first visible worksheet
 

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