Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with loop-I'm stuck
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Help with loop-I'm stuck
I am trying to loop through a pivot table to look for values by row. The
number of rows and columns will vary.

Basically, if in Row 5 only one cell from Column B to the last column(not
counting the "Grand Total") is not blank then the column after "Grand Total"
should say "First Time".

If Columns C and D are not blank the first column after "grand Total" should
say "Two Times in a Row". etc, etc.

Here's what I have. This is too advanced for me.

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Set lastcol = LastCell.Offset(-1, -1)

lstrw = .Cells(Rows.Count, "a").End(xlUp).Row

For myrow = lstrw To 5 Step -1

For Each Row In myrow

If Range("B:" & lastcol).Value 'stuck here


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Help with loop-I'm stuck
This is a little different than what you described, but I believe it will do
what you want. Give it a try. You can modify it to suit your propose.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "No Value Found"
End If
Next

End Sub



"mattg" wrote in message
...
I am trying to loop through a pivot table to look for values by row. The
number of rows and columns will vary.

Basically, if in Row 5 only one cell from Column B to the last column(not
counting the "Grand Total") is not blank then the column after "Grand
Total"
should say "First Time".

If Columns C and D are not blank the first column after "grand Total"
should
say "Two Times in a Row". etc, etc.

Here's what I have. This is too advanced for me.

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Set lastcol = LastCell.Offset(-1, -1)

lstrw = .Cells(Rows.Count, "a").End(xlUp).Row

For myrow = lstrw To 5 Step -1

For Each Row In myrow

If Range("B:" & lastcol).Value 'stuck here




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Help with loop-I'm stuck
This might work better:

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol) - 1)) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub





"JLGWhiz" wrote in message
...
This is a little different than what you described, but I believe it will
do what you want. Give it a try. You can modify it to suit your propose.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "No Value Found"
End If
Next

End Sub



"mattg" wrote in message
...
I am trying to loop through a pivot table to look for values by row. The
number of rows and columns will vary.

Basically, if in Row 5 only one cell from Column B to the last column(not
counting the "Grand Total") is not blank then the column after "Grand
Total"
should say "First Time".

If Columns C and D are not blank the first column after "grand Total"
should
say "Two Times in a Row". etc, etc.

Here's what I have. This is too advanced for me.

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Set lastcol = LastCell.Offset(-1, -1)

lstrw = .Cells(Rows.Count, "a").End(xlUp).Row

For myrow = lstrw To 5 Step -1

For Each Row In myrow

If Range("B:" & lastcol).Value 'stuck here






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Help with loop-I'm stuck
I receive a "Method 'Range' of object '_Global' failed" error centering
around the "CountA" operation.


"JLGWhiz" wrote:

This might work better:

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol) - 1)) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub





"JLGWhiz" wrote in message
...
This is a little different than what you described, but I believe it will
do what you want. Give it a try. You can modify it to suit your propose.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "No Value Found"
End If
Next

End Sub



"mattg" wrote in message
...
I am trying to loop through a pivot table to look for values by row. The
number of rows and columns will vary.

Basically, if in Row 5 only one cell from Column B to the last column(not
counting the "Grand Total") is not blank then the column after "Grand
Total"
should say "First Time".

If Columns C and D are not blank the first column after "grand Total"
should
say "Two Times in a Row". etc, etc.

Here's what I have. This is too advanced for me.

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Set lastcol = LastCell.Offset(-1, -1)

lstrw = .Cells(Rows.Count, "a").End(xlUp).Row

For myrow = lstrw To 5 Step -1

For Each Row In myrow

If Range("B:" & lastcol).Value 'stuck here






.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Help with loop-I'm stuck
When trying to allow for the Totals column,
I had the - 1 in the wrong place. This should work.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol - 1))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub











"JLGWhiz" wrote in message
...
This might work better:

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol) - 1)) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub





"JLGWhiz" wrote in message
...
This is a little different than what you described, but I believe it will
do what you want. Give it a try. You can modify it to suit your
propose.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "No Value Found"
End If
Next

End Sub



"mattg" wrote in message
...
I am trying to loop through a pivot table to look for values by row. The
number of rows and columns will vary.

Basically, if in Row 5 only one cell from Column B to the last
column(not
counting the "Grand Total") is not blank then the column after "Grand
Total"
should say "First Time".

If Columns C and D are not blank the first column after "grand Total"
should
say "Two Times in a Row". etc, etc.

Here's what I have. This is too advanced for me.

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Set lastcol = LastCell.Offset(-1, -1)

lstrw = .Cells(Rows.Count, "a").End(xlUp).Row

For myrow = lstrw To 5 Step -1

For Each Row In myrow

If Range("B:" & lastcol).Value 'stuck here








Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27
Default Help with loop-I'm stuck
I see where you made the change but now it is producing an application or
object defined error.

"JLGWhiz" wrote:

When trying to allow for the Totals column,
I had the - 1 in the wrong place. This should work.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol - 1))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub











"JLGWhiz" wrote in message
...
This might work better:

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol) - 1)) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub





"JLGWhiz" wrote in message
...
This is a little different than what you described, but I believe it will
do what you want. Give it a try. You can modify it to suit your
propose.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "No Value Found"
End If
Next

End Sub



"mattg" wrote in message
...
I am trying to loop through a pivot table to look for values by row. The
number of rows and columns will vary.

Basically, if in Row 5 only one cell from Column B to the last
column(not
counting the "Grand Total") is not blank then the column after "Grand
Total"
should say "First Time".

If Columns C and D are not blank the first column after "grand Total"
should
say "Two Times in a Row". etc, etc.

Here's what I have. This is too advanced for me.

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Set lastcol = LastCell.Offset(-1, -1)

lstrw = .Cells(Rows.Count, "a").End(xlUp).Row

For myrow = lstrw To 5 Step -1

For Each Row In myrow

If Range("B:" & lastcol).Value 'stuck here








.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Help with loop-I'm stuck
Well, I did an overhaul and tested it. It seems to be working now. Don't
know what happened before, I thought the other one was working, but I did
not test it after I made the change and that is what happens. So, anyhow,
try this one.

Sub stitute()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells.Find(What:="*", After:=WS.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol - 1))
For Each Rw In rng.Rows
If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol - 1))) 0 Then
WS.Cells(Rw.Row, Lastcol - 1).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol - 1).Offset(0, 1) = "No Value Found"
End If
Next

End Sub




"mattg" wrote in message
...
I see where you made the change but now it is producing an application or
object defined error.

"JLGWhiz" wrote:

When trying to allow for the Totals column,
I had the - 1 in the wrong place. This should work.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol - 1))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub











"JLGWhiz" wrote in message
...
This might work better:

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol) - 1)) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub





"JLGWhiz" wrote in message
...
This is a little different than what you described, but I believe it
will
do what you want. Give it a try. You can modify it to suit your
propose.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "No Value Found"
End If
Next

End Sub



"mattg" wrote in message
...
I am trying to loop through a pivot table to look for values by row.
The
number of rows and columns will vary.

Basically, if in Row 5 only one cell from Column B to the last
column(not
counting the "Grand Total") is not blank then the column after "Grand
Total"
should say "First Time".

If Columns C and D are not blank the first column after "grand Total"
should
say "Two Times in a Row". etc, etc.

Here's what I have. This is too advanced for me.

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Set lastcol = LastCell.Offset(-1, -1)

lstrw = .Cells(Rows.Count, "a").End(xlUp).Row

For myrow = lstrw To 5 Step -1

For Each Row In myrow

If Range("B:" & lastcol).Value 'stuck here








.



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,565
Default Help with loop-I'm stuck
Nuts! I think I am getting road hypnosis and I am not even driving. I just
realized that the last code will wipe out the totals column. Use this one:

Sub stitute()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells.Find(What:="*", After:=WS.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol - 1))
For Each Rw In rng.Rows
If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol - 1))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub








"JLGWhiz" wrote in message
...
Well, I did an overhaul and tested it. It seems to be working now. Don't
know what happened before, I thought the other one was working, but I did
not test it after I made the change and that is what happens. So, anyhow,
try this one.

Sub stitute()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells.Find(What:="*", After:=WS.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol - 1))
For Each Rw In rng.Rows
If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol - 1))) 0 Then
WS.Cells(Rw.Row, Lastcol - 1).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol - 1).Offset(0, 1) = "No Value Found"
End If
Next

End Sub




"mattg" wrote in message
...
I see where you made the change but now it is producing an application or
object defined error.

"JLGWhiz" wrote:

When trying to allow for the Totals column,
I had the - 1 in the wrong place. This should work.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol - 1))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub











"JLGWhiz" wrote in message
...
This might work better:

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol) - 1)) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 1) = "No Value Found"
End If
Next

End Sub





"JLGWhiz" wrote in message
...
This is a little different than what you described, but I believe it
will
do what you want. Give it a try. You can modify it to suit your
propose.

Sub djk()
Dim LastCell As Long, Lastcol As Long
Dim WS As Worksheet
Set WS = ActiveSheet

Lastcol = WS.Cells.Find(What:="*", After:=WS.Range("A5"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
lstRw = WS.Cells(Rows.Count, "a").End(xlUp).Row

Set rng = WS.Range("B5", WS.Cells(lstRw, Lastcol))

For Each Rw In rng.Rows

If Application.CountA(Range("B" & Rw.Row, _
WS.Cells(Rw.Row, Lastcol))) 0 Then
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "Has Value"
Else
WS.Cells(Rw.Row, Lastcol).Offset(0, 2) = "No Value Found"
End If
Next

End Sub



"mattg" wrote in message
...
I am trying to loop through a pivot table to look for values by row.
The
number of rows and columns will vary.

Basically, if in Row 5 only one cell from Column B to the last
column(not
counting the "Grand Total") is not blank then the column after
"Grand
Total"
should say "First Time".

If Columns C and D are not blank the first column after "grand
Total"
should
say "Two Times in a Row". etc, etc.

Here's what I have. This is too advanced for me.

Dim LastCell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.UsedRange.SpecialCells(xlCellTypeLastCell)
Set lastcol = LastCell.Offset(-1, -1)

lstrw = .Cells(Rows.Count, "a").End(xlUp).Row

For myrow = lstrw To 5 Step -1

For Each Row In myrow

If Range("B:" & lastcol).Value 'stuck here








.





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
stuck in loop Trish Smith Excel Programming 6 September 19th 08 11:31 AM
stuck at a loop computers hate me Excel Programming 6 August 4th 08 02:19 PM
Stuck in a Sub Loop robzrob Excel Worksheet Functions 4 July 19th 08 11:53 PM
Stuck in a loop CWillis Excel Discussion (Misc queries) 2 June 5th 06 02:49 PM
HELP - Stuck in loop gti_jobert[_92_] Excel Programming 3 April 27th 06 01:41 PM


All times are GMT +1. The time now is 11:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement