Hey Torstein,
Interesting problem... Is the range always the same? Are there any other non zero values? Is it possible to add values to the row beneath or on top of row 10... or somewhere else even?
I'm trying LookUp with > 0 but I'm not hopeful :-)
You may have to go for a long If with IF > 0 for the whole range. That's why I ask if the range is fixed.
I'm playing with it anyway.
HTH
Lisa
Sent: Sun, Dec 16, 2012 3:19 pm
Subject: 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]
------------------------------------
----------------------------------
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
[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 (4) |
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