vba..finding first non-zero value in a row
 

Hi,

I'm trying to find some VBA code that will allow me to start in one cell,
say B23, and go right until it finds a cell that has a non-zero value.
Finally, it should sum the next X number of cells to the right.

Can anyone help?

THANKS!

vba..finding first non-zero value in a row
 

Try this Andrew

Sub test()
Dim col As Long
col = Range("B23").End(xlToRight).Offset(0, 1).Column
MsgBox Application.Sum(Range(Cells(23, col), Cells(23, 256)))
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Andrew Appel" wrote in message ...
Hi,

I'm trying to find some VBA code that will allow me to start in one cell,
say B23, and go right until it finds a cell that has a non-zero value.
Finally, it should sum the next X number of cells to the right.

Can anyone help?

THANKS!

vba..finding first non-zero value in a row
 

Since you don't say the cells to the right are empty,

Sub AAAtester2()
Dim rng As Range, icol As Long
Dim dblSum As Double, x As Long
x = 10
Set rng = Range(Cells(23, 2), Cells(23, 256))
icol = Evaluate("Small(If((" & rng.Address & "<0)*(" & _
rng.Address & "<""""),Column(" & rng.Address & ")),1)")
dblSum = Application.Sum(Cells(23, icol).Resize(1, x))
MsgBox dblSum
End Sub

--
Regards,
Tom Ogilvy






Andrew Appel wrote in message
...
Hi,

I'm trying to find some VBA code that will allow me to start in one cell,
say B23, and go right until it finds a cell that has a non-zero value.
Finally, it should sum the next X number of cells to the right.

Can anyone help?

THANKS!

vba..finding first non-zero value in a row
 

Thank you both! the cells to the right aren't empty, just zero. I really
appreciate it.
Andrew
"Tom Ogilvy" wrote in message
...
Since you don't say the cells to the right are empty,

Sub AAAtester2()
Dim rng As Range, icol As Long
Dim dblSum As Double, x As Long
x = 10
Set rng = Range(Cells(23, 2), Cells(23, 256))
icol = Evaluate("Small(If((" & rng.Address & "<0)*(" & _
rng.Address & "<""""),Column(" & rng.Address & ")),1)")
dblSum = Application.Sum(Cells(23, icol).Resize(1, x))
MsgBox dblSum
End Sub

--
Regards,
Tom Ogilvy






Andrew Appel wrote in message
...
Hi,

I'm trying to find some VBA code that will allow me to start in one
cell,
say B23, and go right until it finds a cell that has a non-zero value.
Finally, it should sum the next X number of cells to the right.

Can anyone help?

THANKS!