Minggu, 16 Desember 2012

Vedr: [ExcelVBA] Find position of first non-zero in range

 

Hi Dave, thank you!

I know that solution, but I want to have a solution that can be saved as a non-macro workbook. I suppose I can't use UDF in such a workbook!?

Thats why I was looking for a formula solution, although I know I'm asking av VBA group.

regards Torstein

________________________________
Fra: David Smart <smartware.consulting@gmail.com>
Til: ExcelVBA@yahoogroups.com
Sendt: Søndag, 16. desember 2012 14.46
Emne: Re: [ExcelVBA] Find position of first non-zero in range


 
Do you want the position as a number, or do you want the cell reference.

If a number, like the sub, then

Change the sub to a function returning an integer.

Instead of the MsgBox, assign teller to the name of the function.

Then use the function call in a formula.

E.g.

Function finnførsteikke0() as Integer
teller = 0
While range("c10").Offset(0, teller) = 0
teller = teller + 1
Wend
finnførsteikke0= teller + 1
End Function

Regards, Dave S

----- Original Message -----
From: "Torstein Johnsen" <sejohnse@yahoo.no>
To: <ExcelVBA@yahoogroups.com>
Sent: Sunday, December 16, 2012 11:16 PM
Subject: [ExcelVBA] Find position of first non-zero in range

This procedure helps me find the position of the first non-zero value in a
range C10:K10. There will always be at least one.

Sub finnførsteikke0()

teller = 0

While range("c10").Offset(0, teller) = 0
teller = teller + 1
Wend

teller = teller + 1

MsgBox (teller)

End Sub

Can any of you help me with a formula giving me the same answer?

Thank you!

regards

Torstein
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us
Feedback
.

[Non-text portions of this message have been removed]

------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1430 / Virus Database: 2634/5463 - Release Date: 12/15/12

[Non-text portions of this message have been removed]

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___

Tidak ada komentar:

Posting Komentar