Help with loop-I'm stuck

[mattg]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[mattg]

I receive a "Method 'Range' of object '_Global' failed" error centering
around the "CountA" operation.

 

[JLGWhiz]

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

 

[mattg]

I see where you made the change but now it is producing an application or
object defined error.

 

[JLGWhiz]

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

 

[JLGWhiz]

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