For... Next loop problem
 

Starting at A4 and every 8th cell after that down the A column I have a
formula (=A5 for example). The formula looks for a name (text) in the cell
just below it. If a name is there then that name shows in the cell with the
formula. If no name is present then the cell with the formula shows a 0. In
my For...Next loop below I want to cycle through each cell looking for that
zero. But the If...Then statement holds true the first time through
everytime... even if a name is showing in the cell and not a zero. So no
matter what cell I start in THAT cell has a value of 0... according to the
If...Then statement. The spreadsheet I'm testing doesn't have a 0 until A92.
What am I doing wrong?

Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long
Dim ZeroRow As Integer

For Each wb In Workbooks
'Test to see if wb's name is like "*C&A PF*"
'Should have all weeks for the particular month open
If wb.Name Like "*C&A PF*" Then
'Create a worksheet object to reference the appropriate
'worksheet in the wb
Set ws = wb.Worksheets("Tally sheet")

With ws
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next

For... Next loop problem
 

Untested:

For i = 4 To Rows.Count Step 8
If IsNumber(ws.Cells(i, "A") And ws.Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next



"Bishop" wrote in message
...
Starting at A4 and every 8th cell after that down the A column I have a
formula (=A5 for example). The formula looks for a name (text) in the
cell
just below it. If a name is there then that name shows in the cell with
the
formula. If no name is present then the cell with the formula shows a 0.
In
my For...Next loop below I want to cycle through each cell looking for
that
zero. But the If...Then statement holds true the first time through
everytime... even if a name is showing in the cell and not a zero. So no
matter what cell I start in THAT cell has a value of 0... according to the
If...Then statement. The spreadsheet I'm testing doesn't have a 0 until
A92.
What am I doing wrong?

Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long
Dim ZeroRow As Integer

For Each wb In Workbooks
'Test to see if wb's name is like "*C&A PF*"
'Should have all weeks for the particular month open
If wb.Name Like "*C&A PF*" Then
'Create a worksheet object to reference the appropriate
'worksheet in the wb
Set ws = wb.Worksheets("Tally sheet")

With ws
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next

For... Next loop problem
 

You forgot to include

..Cells( ... )

In your With WS statement. (missed the leading period)

HTH,
Barb Reinhardt

"Bishop" wrote:

Starting at A4 and every 8th cell after that down the A column I have a
formula (=A5 for example). The formula looks for a name (text) in the cell
just below it. If a name is there then that name shows in the cell with the
formula. If no name is present then the cell with the formula shows a 0. In
my For...Next loop below I want to cycle through each cell looking for that
zero. But the If...Then statement holds true the first time through
everytime... even if a name is showing in the cell and not a zero. So no
matter what cell I start in THAT cell has a value of 0... according to the
If...Then statement. The spreadsheet I'm testing doesn't have a 0 until A92.
What am I doing wrong?

Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long
Dim ZeroRow As Integer

For Each wb In Workbooks
'Test to see if wb's name is like "*C&A PF*"
'Should have all weeks for the particular month open
If wb.Name Like "*C&A PF*" Then
'Create a worksheet object to reference the appropriate
'worksheet in the wb
Set ws = wb.Worksheets("Tally sheet")

With ws
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next

For... Next loop problem
 

Just noticed something else (.Rows ... )

With ws
For i = 4 To .Rows.Count Step 8
If .Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next i
End With

"Bishop" wrote:

Starting at A4 and every 8th cell after that down the A column I have a
formula (=A5 for example). The formula looks for a name (text) in the cell
just below it. If a name is there then that name shows in the cell with the
formula. If no name is present then the cell with the formula shows a 0. In
my For...Next loop below I want to cycle through each cell looking for that
zero. But the If...Then statement holds true the first time through
everytime... even if a name is showing in the cell and not a zero. So no
matter what cell I start in THAT cell has a value of 0... according to the
If...Then statement. The spreadsheet I'm testing doesn't have a 0 until A92.
What am I doing wrong?

Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long
Dim ZeroRow As Integer

For Each wb In Workbooks
'Test to see if wb's name is like "*C&A PF*"
'Should have all weeks for the particular month open
If wb.Name Like "*C&A PF*" Then
'Create a worksheet object to reference the appropriate
'worksheet in the wb
Set ws = wb.Worksheets("Tally sheet")

With ws
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next