How to return multiple cells?

[OKLover]

The code as below:

Set fCell = Columns("Q:Q").Cells
Set aCell = fCell.Find(What:=0, LookIn:=xlValues, After:=Range("Q3"), _
SearchOrder:=xlByColumns)
MsgBox aCell.Cells.Count

If there are more than one cell are 0 value, The Msgbox always return 1.
What i can do? so it may be return the range which cell contain 0 value.

Many Thanks

[Ron de Bruin]

OKLover

It will find the first cell with a 0 after Q3
aCell.address will give you the cell

If you want to have a range with all 0 values you can use Union
Example for A1:A100

Sub UnionExample()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 100
For Lrow = StartRow To EndRow Step 1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value = "0" Then
If rng Is Nothing Then
Set rng = .Cells(Lrow, "A")
Else
Set rng = Application.Union(rng, .Cells(Lrow, "A"))
End If
End If
Next
End With

'Select all rows with a 0
If Not rng Is Nothing Then
rng.Select
MsgBox rng.Address
End If

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"OKLover" <OKLover@discussions.microsoft.com> wrote in message news:C8EE18B9-40B9-4195-8E60-95B2C51D8974@microsoft.com...
> The code as below:
>
> Set fCell = Columns("Q:Q").Cells
> Set aCell = fCell.Find(What:=0, LookIn:=xlValues, After:=Range("Q3"), _
> SearchOrder:=xlByColumns)
> MsgBox aCell.Cells.Count
>
> If there are more than one cell are 0 value, The Msgbox always return 1.
> What i can do? so it may be return the range which cell contain 0 value.
>
> Many Thanks