Find not equal to "0"
 

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

Find not equal to "0"
 

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.

Mark wrote:

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

--

Dave Peterson

Find not equal to "0"
 

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





"Mark" wrote in message
...
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

Find not equal to "0"
 

Many thanks Joerg

I'll give it a go

Mark

"Joerg" wrote in message
...
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





"Mark" wrote in message
...
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

Find not equal to "0"
 

Many thanks Joerg

I'll give it a go

Mark

"Joerg" wrote in message
...
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





"Mark" wrote in message
...
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

Find not equal to "0"
 

Would take too long Dave

Cheers

"Dave Peterson" wrote in message
...
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.

Mark wrote:

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

--

Dave Peterson

Find not equal to "0"
 

On Fri, 8 Jun 2007 11:37:43 +1000, "Mark" wrote:

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


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

Find not equal to "0"
 

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.

Mark wrote:

Would take too long Dave

Cheers

"Dave Peterson" wrote in message
...
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.

Mark wrote:

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

--

Dave Peterson

--

Dave Peterson

Find not equal to "0"
 

Errrrr. This is looping through the cells.

Mark wrote:

Many thanks Joerg

I'll give it a go

Mark

"Joerg" wrote in message
...
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





"Mark" wrote in message
...
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




--

Dave Peterson