Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For...Each loop problem | Excel Programming | |||
For...Each loop problem | Excel Programming | |||
Loop Within a Loop Problem | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |