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

  • Thread starter Thread starter Andrew Appel
  • Start date Start date
A

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!
 
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
 
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
 
Thank you both! the cells to the right aren't empty, just zero. I really
appreciate it.
Andrew
 
Back
Top