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

[Andrew Appel]

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!

 

[Ron de Bruin]

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

 

[Tom Ogilvy]

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

 

[Andrew Appel]

Thank you both! the cells to the right aren't empty, just zero. I really
appreciate it.
Andrew