Function to find the first cell that is not zero
 

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is there perchance a function that will do this?

Mark

Function to find the first cell that is not zero
 

Function nonzero(r As Range) As String
nonzero = ""
For Each rr In r
If rr.Value < 0 Then
nonzero = rr.Address
Exit Function
End If
Next
End Function

so if in row 17, F17 contains the first non-zero value then:

=nonzero(17:17)

will return

$F$17
--
Gary''s Student - gsnu200727


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is there perchance a function that will do this?

Mark

Function to find the first cell that is not zero
 

You programmers always want to reinvent the wheel!

Biff

"Gary''s Student" wrote in message
...
Function nonzero(r As Range) As String
nonzero = ""
For Each rr In r
If rr.Value < 0 Then
nonzero = rr.Address
Exit Function
End If
Next
End Function

so if in row 17, F17 contains the first non-zero value then:

=nonzero(17:17)

will return

$F$17
--
Gary''s Student - gsnu200727


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

You are, of course, correct.

You have proved to me over and over in your posts that you can do things
with formulas that I though to be completely impossible without VBA. I have
actually bookmarked a Google Groups search of your material.
--
Gary''s Student - gsnu200728


"T. Valko" wrote:

You programmers always want to reinvent the wheel!

Biff

"Gary''s Student" wrote in message
...
Function nonzero(r As Range) As String
nonzero = ""
For Each rr In r
If rr.Value < 0 Then
nonzero = rr.Address
Exit Function
End If
Next
End Function

so if in row 17, F17 contains the first non-zero value then:

=nonzero(17:17)

will return

$F$17
--
Gary''s Student - gsnu200727


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

I have actually bookmarked a Google Groups
search of your material.

If I were you I'd change the "search for author" criteria to Harlan Grove.

Biff

"Gary''s Student" wrote in message
...
You are, of course, correct.

You have proved to me over and over in your posts that you can do things
with formulas that I though to be completely impossible without VBA. I
have
actually bookmarked a Google Groups search of your material.
--
Gary''s Student - gsnu200728


"T. Valko" wrote:

You programmers always want to reinvent the wheel!

Biff

"Gary''s Student" wrote in
message
...
Function nonzero(r As Range) As String
nonzero = ""
For Each rr In r
If rr.Value < 0 Then
nonzero = rr.Address
Exit Function
End If
Next
End Function

so if in row 17, F17 contains the first non-zero value then:

=nonzero(17:17)

will return

$F$17
--
Gary''s Student - gsnu200727


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

Domenic is a good search criteria as well, he is not as polite as Harlan
though :))


--
Regards,

Peo Sjoblom


"T. Valko" wrote in message
...
I have actually bookmarked a Google Groups
search of your material.

If I were you I'd change the "search for author" criteria to Harlan Grove.

Biff

"Gary''s Student" wrote in
message ...
You are, of course, correct.

You have proved to me over and over in your posts that you can do things
with formulas that I though to be completely impossible without VBA. I
have
actually bookmarked a Google Groups search of your material.
--
Gary''s Student - gsnu200728


"T. Valko" wrote:

You programmers always want to reinvent the wheel!

Biff

"Gary''s Student" wrote in
message
...
Function nonzero(r As Range) As String
nonzero = ""
For Each rr In r
If rr.Value < 0 Then
nonzero = rr.Address
Exit Function
End If
Next
End Function

so if in row 17, F17 contains the first non-zero value then:

=nonzero(17:17)

will return

$F$17
--
Gary''s Student - gsnu200727


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

LOL!

Biff

"Peo Sjoblom" wrote in message
...
Domenic is a good search criteria as well, he is not as polite as Harlan
though :))


--
Regards,

Peo Sjoblom


"T. Valko" wrote in message
...
I have actually bookmarked a Google Groups
search of your material.

If I were you I'd change the "search for author" criteria to Harlan
Grove.

Biff

"Gary''s Student" wrote in
message ...
You are, of course, correct.

You have proved to me over and over in your posts that you can do things
with formulas that I though to be completely impossible without VBA. I
have
actually bookmarked a Google Groups search of your material.
--
Gary''s Student - gsnu200728


"T. Valko" wrote:

You programmers always want to reinvent the wheel!

Biff

"Gary''s Student" wrote in
message
...
Function nonzero(r As Range) As String
nonzero = ""
For Each rr In r
If rr.Value < 0 Then
nonzero = rr.Address
Exit Function
End If
Next
End Function

so if in row 17, F17 contains the first non-zero value then:

=nonzero(17:17)

will return

$F$17
--
Gary''s Student - gsnu200727


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I
am
also
trying a VBA solution, but is there perchance a function that will
do
this?

Mark

Function to find the first cell that is not zero
 

Many thanks. I'll try it after I get home agian.

Cheers


"Gary''s Student" wrote in message
...
Function nonzero(r As Range) As String
nonzero = ""
For Each rr In r
If rr.Value < 0 Then
nonzero = rr.Address
Exit Function
End If
Next
End Function

so if in row 17, F17 contains the first non-zero value then:

=nonzero(17:17)

will return

$F$17
--
Gary''s Student - gsnu200727


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark