ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help with loop-I'm stuck (https://www.excelbanter.com/excel-programming/441936-help-loop-im-stuck.html)

MattG
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

JLGWhiz[_2_]
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

JLGWhiz[_2_]
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

MattG
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






.

JLGWhiz[_2_]
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

MattG
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








.

JLGWhiz[_2_]
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








.

JLGWhiz[_2_]
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








.


All times are GMT +1. The time now is 04:07 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com