steping through all cells
 

I am trying to analize all cells, one by one. I have tried this code:

For Each Sheet In ThisWorkbook.Sheets
For Each Row In Sheet.Rows
For Each cell In Row
MsgBox cell.Text
Next
Next
Next

But I get a "non valid use of Null" error. Any hints ? Thanks,

steping through all cells
 

Hi Lars,

Try changing:

For Each cell In Row


to

For Each cell In Row,Cells


---
Regards,
Norman


"Lars" wrote in message
...
I am trying to analize all cells, one by one. I have tried this code:

For Each Sheet In ThisWorkbook.Sheets
For Each Row In Sheet.Rows
For Each cell In Row
MsgBox cell.Text
Next
Next
Next

But I get a "non valid use of Null" error. Any hints ? Thanks,

steping through all cells
 

Hi Lars,

Typo warning:

For Each cell In Row,Cells

should read:

For Each cell In Row.Cells



---
Regards,
Norman

steping through all cells
 

Norman,

For Each cell In Row.Cells worked fine, thanks :-)

But now all cells are shown, not just the ones that have values into them.

How may I select just the ones that have a value ? Thanks!

steping through all cells
 

Hi Lars,

Perhaps try:

'=============
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rw As Range
Dim rCell As Range

Set SH = ActiveSheet '<<==== CHANGE

On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rw In rng.Rows
For Each rCell In rw.Cells
With rCell
MsgBox .Value & vbTab & .Address
End With
Next
Next
End If
End Sub
'<<=============


---
Regards,
Norman


"Lars" wrote in message
...
Norman,

For Each cell In Row.Cells worked fine, thanks :-)

But now all cells are shown, not just the ones that have values into them.

How may I select just the ones that have a value ? Thanks!

steping through all cells
 

Hi Lars,

That code will not return formula cells. To process (only) formula cells,
change:

Set rng = SH.Cells.SpecialCells(xlCellTypeConstants)
to
Set rng = SH.Cells.SpecialCells(xlCellTypeConstants)

and, to return the formula, rather than the result of the formula, change:

MsgBox .Value & vbTab & .Address
to
MsgBox .Formula & vbTab & .Address


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Lars,

Perhaps try:

'=============
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rw As Range
Dim rCell As Range

Set SH = ActiveSheet '<<==== CHANGE

On Error Resume Next
Set rng = SH.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rw In rng.Rows
For Each rCell In rw.Cells
With rCell
MsgBox .Value & vbTab & .Address
End With
Next
Next
End If
End Sub
'<<=============


---
Regards,
Norman


"Lars" wrote in message
...
Norman,

For Each cell In Row.Cells worked fine, thanks :-)

But now all cells are shown, not just the ones that have values into
them.

How may I select just the ones that have a value ? Thanks!

steping through all cells
 

Hi Lars,

And to return all populated cells, try;

'=============
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim rw As Range
Dim rCell As Range

Set SH = ActiveSheet '<<==== CHANGE

Set rng = SH.UsedRange

For Each rw In rng.Rows
For Each rCell In rw.Cells
With rCell
If Not IsEmpty(.Value) Then
MsgBox .Formula & vbTab & .Address
End If
End With
Next
Next
End Sub
'<<=============


---
Regards,
Norman

steping through all cells
 

Norman,

Thanks, I was able to get it working this way:

For Each Sheet In ThisWorkbook.Sheets
Set region = Sheet.Cells.SpecialCells(xlCellTypeConstants)
For Each Row In region.Rows
For Each cell In Row.Cells
If InStr(cell.Text, "abc") Then
MsgBox cell.Text
Rem Here I need to retrieve a specific Row cell
End If
Next
Next
Next

Now, how may I select a specific cell from the Row, i.e.: Row.Cell[ 3 ] ?

Thanks!

steping through all cells
 

Hi Lars,

Try:

'=============
Public Sub Tester005()
Dim Sheet As Worksheet
Dim row As Range
Dim cell As Range
Dim region As Range
Const col As String = "C"

For Each Sheet In ThisWorkbook.Sheets
With Sheet
On Error Resume Next
Set region = Intersect(.Columns(col), .Cells. _
SpecialCells(xlCellTypeConstants))
On Error GoTo 0
End With

If Not region Is Nothing Then
For Each row In region.Rows
For Each cell In row.Cells
If InStr(cell.Text, "abc") Then
MsgBox cell.Text
End If
Next cell
Next row
End If
Next Sheet
End Sub
'<<=============

Note that if you use the SpecialCells method, you should include an error
handler to deal with the error if no cells are found.

Note also, that I explicitly dim all variables. In this connection, see Chip
Pearson's comments at:

http://www.cpearson.com/excel/variables.htm



---
Regards,
Norman


"Lars" wrote in message
...
Norman,

Thanks, I was able to get it working this way:

For Each Sheet In ThisWorkbook.Sheets
Set region = Sheet.Cells.SpecialCells(xlCellTypeConstants)
For Each Row In region.Rows
For Each cell In Row.Cells
If InStr(cell.Text, "abc") Then
MsgBox cell.Text
Rem Here I need to retrieve a specific Row cell
End If
Next
Next
Next

Now, how may I select a specific cell from the Row, i.e.: Row.Cell[ 3 ] ?

Thanks!

steping through all cells
 

Norman,

I am not using any formulas in the ExcelBook, so I tried this code to
retrieve all the cells in the book, but I don't get them in the proper
order: on each row - each cell.

For Each Sheet In ThisWorkbook.Sheets
Set region = Sheet.Cells.SpecialCells(xlCellTypeConstants)
For Each Row In region.Rows
For Each cell In Row.Cells
MsgBox cell.Text 'shouldn't I see all rows, cell by cell ?
Next
Next
Next

Thanks!

steping through all cells
 

Hi Lars,

For me, your code traverses each sheet, row by row, e.g.:

A1 B1 E1 K1
A2 E2 J2
A4 B4

I do not, therefore understand your comment:

'shouldn't I see all rows, cell by cell ?


---
Regards,
Norman



"Lars" wrote in message
...
Norman,

I am not using any formulas in the ExcelBook, so I tried this code to
retrieve all the cells in the book, but I don't get them in the proper
order: on each row - each cell.

For Each Sheet In ThisWorkbook.Sheets
Set region = Sheet.Cells.SpecialCells(xlCellTypeConstants)
For Each Row In region.Rows
For Each cell In Row.Cells
MsgBox cell.Text 'shouldn't I see all rows, cell by cell
?
Next
Next
Next

Thanks!