Find not equal to "0"

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi

I want to find the first cell that is not zero "0" across a row. I have
tried the following but is gives a syntax error. (I've searched this forum
but cannot find anything that helps me)

Cells.Find(What:<>"0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

Any help will be greatly appreciated.

Mark
 
There's nothing like find something not equal to in excel.

Since it's only a single row, maybe you can just go through each column until
you find what you want.
 
Well, I guess there are ways in Excel to find something not equal to in
Excel. The following macro selects the first non-zero value in each row of
a selected range. If just a single cell is selected, the macro will select
such non-zero values for the whole worksheet.

Cheers,

Joerg Mochikun

Sub FindFirstNonZeroInEachRow()
Dim rng, MyArea As Range
Dim RowNumber
If Selection.Cells.Count = 1 Then
Set rng = ActiveSheet.UsedRange
Else
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
End If
For Each cell In rng
If cell.Value <> 0 And RowNumber <> cell.Row Then
If MyArea Is Nothing Then Set MyArea = cell
Set MyArea = Union(MyArea, cell)
RowNumber = cell.Row
End If
Next cell
If MyArea Is Nothing Then
MsgBox "No non-zero values found!"
Else
MyArea.Select
End If
End Sub
 
Many thanks Joerg

I'll give it a go

Mark

Joerg said:
Well, I guess there are ways in Excel to find something not equal to in
Excel. The following macro selects the first non-zero value in each row
of a selected range. If just a single cell is selected, the macro will
select such non-zero values for the whole worksheet.

Cheers,

Joerg Mochikun

Sub FindFirstNonZeroInEachRow()
Dim rng, MyArea As Range
Dim RowNumber
If Selection.Cells.Count = 1 Then
Set rng = ActiveSheet.UsedRange
Else
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
End If
For Each cell In rng
If cell.Value <> 0 And RowNumber <> cell.Row Then
If MyArea Is Nothing Then Set MyArea = cell
Set MyArea = Union(MyArea, cell)
RowNumber = cell.Row
End If
Next cell
If MyArea Is Nothing Then
MsgBox "No non-zero values found!"
Else
MyArea.Select
End If
End Sub
Click to expand...
 
Many thanks Joerg

I'll give it a go

Mark

Joerg said:
Well, I guess there are ways in Excel to find something not equal to in
Excel. The following macro selects the first non-zero value in each row
of a selected range. If just a single cell is selected, the macro will
select such non-zero values for the whole worksheet.

Cheers,

Joerg Mochikun

Sub FindFirstNonZeroInEachRow()
Dim rng, MyArea As Range
Dim RowNumber
If Selection.Cells.Count = 1 Then
Set rng = ActiveSheet.UsedRange
Else
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
End If
For Each cell In rng
If cell.Value <> 0 And RowNumber <> cell.Row Then
If MyArea Is Nothing Then Set MyArea = cell
Set MyArea = Union(MyArea, cell)
RowNumber = cell.Row
End If
Next cell
If MyArea Is Nothing Then
MsgBox "No non-zero values found!"
Else
MyArea.Select
End If
End Sub
Click to expand...
 
Would take too long Dave

Cheers

Dave Peterson said:
There's nothing like find something not equal to in excel.

Since it's only a single row, maybe you can just go through each column
until
you find what you want.
Click to expand...
 
Hi

I want to find the first cell that is not zero "0" across a row. I have
tried the following but is gives a syntax error. (I've searched this forum
but cannot find anything that helps me)

Cells.Find(What:<>"0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

Any help will be greatly appreciated.

Mark
Click to expand...

Do you want to limit the result to numeric values, or will anything that is not
a zero suffice? What about Blanks?

Do you want to return the value, or the cell address?

Is the non-zero value a constant? or the result of a formula?


--ron
 
Did you try looping through 256 columns (xl2003 and below) or 16k in xl2007--or
even just through the usedrange.

I'm surprised that you could notice the delay.
 
Back
Top