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.

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

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

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

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

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

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

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

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