Empty cells in For Loop
 

I am trying to write a For Loop which runs all the populated cells in the
range through a calculation. My question is how do I make sure that empty or
null cells aren't selected to be used in the calculation? Essentially I just
want to ignore the empty/null cells. Below is the code for the loop in case
this helps:

For Each A In range1
For Each B In range1
If B.Value < A.Value Then
If Abs(A - B) <= testvalue Then
Sheets("Test Results").Select
range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.Value = "Interference"
ActiveCell.Offset(0, 1).Select
Selection.Value = A.Value
ActiveCell.Offset(0, 1).Select
Selection.Value = B.Value
End If
End If
Next
Next

Empty cells in For Loop
 

Do you mean that if a cell is empty then you don't care about the rest, that
is if an A is empty, you ignore that and all associated B's?

If so

For Each A In range1
If Len(A.Value) 0 Then
For Each B In range1
If Len(B.Value) 0 Then
If B.Value < A.Value Then
If Abs(A.Value - B.Value) <= testvalue Then
With Sheets("Test
Results").Range("A1").End(xlDown)
.Offset(1, 0).Value = "Interference"
.Offset(0, 1).Value = A.Value
.Offset(0, 1).Value = B.Value
End With
End If
End If
End If
Next B
End If
Next A


--
HTH

Bob Phillips

"Linking to specific cells in pivot table"
crosoft.com wrote in
message ...
I am trying to write a For Loop which runs all the populated cells in the
range through a calculation. My question is how do I make sure that empty
or
null cells aren't selected to be used in the calculation? Essentially I
just
want to ignore the empty/null cells. Below is the code for the loop in
case
this helps:

For Each A In range1
For Each B In range1
If B.Value < A.Value Then
If Abs(A - B) <= testvalue Then
Sheets("Test Results").Select
range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.Value = "Interference"
ActiveCell.Offset(0, 1).Select
Selection.Value = A.Value
ActiveCell.Offset(0, 1).Select
Selection.Value = B.Value
End If
End If
Next
Next

Empty cells in For Loop
 

Try the following approach. Note the major "Union" line added.
Also, a "Range" variable called "tmp" is added.

The macro minimize the scope to only those cells in the range with either
value or formula. All empty cells are ignored. Therefore, processing time
will be minimized.



Dim range1 As Range
Dim tmp As Range
Dim A As Object, B As Object
Dim testvalue

With range1
Set tmp = Union(.SpecialCells(2), .SpecialCells(-4123))
End With
For Each A In tmp
For Each B In tmp
If B.Value < A.Value Then
If IsNumeric(A) And IsNumeric(B) Then
If Abs(A - B) <= testvalue Then
With Worksheets("Test Results").Range("A1")
.End(xlDown).Offset(1, 0).Value = "Interference"
.End(xlDown).Offset(0, 1).Value = A.Value
.End(xlDown).Offset(0, 2).Value = B.Value
End With
End If
End If
End If
Next
Next


Regards,
Edwin Tam

http://www.vonixx.com



"Linking to specific cells in pivot table" wrote:

I am trying to write a For Loop which runs all the populated cells in the
range through a calculation. My question is how do I make sure that empty or
null cells aren't selected to be used in the calculation? Essentially I just
want to ignore the empty/null cells. Below is the code for the loop in case
this helps:

For Each A In range1
For Each B In range1
If B.Value < A.Value Then
If Abs(A - B) <= testvalue Then
Sheets("Test Results").Select
range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.Value = "Interference"
ActiveCell.Offset(0, 1).Select
Selection.Value = A.Value
ActiveCell.Offset(0, 1).Select
Selection.Value = B.Value
End If
End If
Next
Next