Any Help with a Simple Function?
 

Hello All,
I am having the hardest time coming up with an answer to what is
seemingly the easiest function of Excel. I have Loop Until Statement that
gets me the absolute value of a column and lists it into the next column
over. I have it looped because there is no definitive ending point of how
many rows there may be, one day 5, the next day 205. I can get that done,
but what seems the most simple, I just can't figure out. How do I code it
to Sum the total of the 2nd column? Here is the code I have:

Sub Count_Cases()
Range("M5").Select
Do
ActiveCell.Value = Abs(ActiveCell.Offset(0, -1))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
<<<Here is where I need it to Total all of the values in this column
starting with "M5" through: ActiveCell.Offset (-1,0){This is where I get
confused, I can't define the Active Cell and use FormulaR1C1, or can I?}
End Sub

Any ideas would be greatly appreciated.

Jacob

Any Help with a Simple Function?
 

How about this one. But i think this one not so smart because using select method.

Sub Count_Cases1()
Range("M5").Select
Do
Selection.Value = Abs(Selection.Offset(0, -1))
Selection.Offset(1, 0).Select
Loop Until IsEmpty(Selection.Offset(0, -1))
End Sub

keizi

"Jacob G" wrote in message
...
Hello All,
I am having the hardest time coming up with an answer to what is
seemingly the easiest function of Excel. I have Loop Until Statement that
gets me the absolute value of a column and lists it into the next column
over. I have it looped because there is no definitive ending point of how
many rows there may be, one day 5, the next day 205. I can get that done,
but what seems the most simple, I just can't figure out. How do I code it
to Sum the total of the 2nd column? Here is the code I have:

Sub Count_Cases()
Range("M5").Select
Do
ActiveCell.Value = Abs(ActiveCell.Offset(0, -1))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
<<<Here is where I need it to Total all of the values in this column
starting with "M5" through: ActiveCell.Offset (-1,0){This is where I get
confused, I can't define the Active Cell and use FormulaR1C1, or can I?}
End Sub

Any ideas would be greatly appreciated.

Jacob

Any Help with a Simple Function?
 

Maybe easier without the loop:

Sub Count_Cases()
Dim eRow As Long

eRow = Cells(Rows.Count, "L").End(xlUp).Row
With Range("M5:M" & eRow)
.FormulaR1C1 = "=ABS(RC[-1])"
.Value = .Value
End With
Cells(eRow + 1, "M").FormulaR1C1 = _
"=SUM(R[-" & eRow - 4 & "]C:R[-1]C)"
End Sub

Hope this helps
Rowan

Jacob G wrote:
Hello All,
I am having the hardest time coming up with an answer to what is
seemingly the easiest function of Excel. I have Loop Until Statement that
gets me the absolute value of a column and lists it into the next column
over. I have it looped because there is no definitive ending point of how
many rows there may be, one day 5, the next day 205. I can get that done,
but what seems the most simple, I just can't figure out. How do I code it
to Sum the total of the 2nd column? Here is the code I have:

Sub Count_Cases()
Range("M5").Select
Do
ActiveCell.Value = Abs(ActiveCell.Offset(0, -1))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
<<<Here is where I need it to Total all of the values in this column
starting with "M5" through: ActiveCell.Offset (-1,0){This is where I get
confused, I can't define the Active Cell and use FormulaR1C1, or can I?}
End Sub

Any ideas would be greatly appreciated.

Jacob

Any Help with a Simple Function?
 

Thanks,
But this didn't work, it only modified the way that it looked at the
looped values. The loop works fine and gets the result I need and ends on
the correct cell I need. Basically, after the Loop Until Statement, I need
another statement that will give me a total of the value of the cells above
the active cell. It would be like clicking the "AutoSum" Button on the
toolbar after the loop has finished.
The last statement below, One Line above the End Sub is where I can't get it
to work.

Sub Count_Cases1()
Range("M5").Select
Do
ActiveCell.Value = Abs(Selection.Offset(0, -1))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(Selection.Offset(0, -1))
ActiveCell = Sum (Range("M5", ActiveCell.Offset(-1,0)))
End Sub

"kounoike" wrote in message
...
How about this one. But i think this one not so smart because using select
method.

Sub Count_Cases1()
Range("M5").Select
Do
Selection.Value = Abs(Selection.Offset(0, -1))
Selection.Offset(1, 0).Select
Loop Until IsEmpty(Selection.Offset(0, -1))
End Sub

keizi

"Jacob G" wrote in message
...
Hello All,
I am having the hardest time coming up with an answer to what is
seemingly the easiest function of Excel. I have Loop Until Statement
that
gets me the absolute value of a column and lists it into the next column
over. I have it looped because there is no definitive ending point of
how
many rows there may be, one day 5, the next day 205. I can get that
done,
but what seems the most simple, I just can't figure out. How do I code
it
to Sum the total of the 2nd column? Here is the code I have:

Sub Count_Cases()
Range("M5").Select
Do
ActiveCell.Value = Abs(ActiveCell.Offset(0, -1))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
<<<Here is where I need it to Total all of the values in this column
starting with "M5" through: ActiveCell.Offset (-1,0){This is where I get
confused, I can't define the Active Cell and use FormulaR1C1, or can I?}
End Sub

Any ideas would be greatly appreciated.

Jacob

Any Help with a Simple Function?
 

Rowan,
This worked Perfectly, Thank You Very Much!!

"Rowan Drummond" wrote in message
...
Maybe easier without the loop:

Sub Count_Cases()
Dim eRow As Long

eRow = Cells(Rows.Count, "L").End(xlUp).Row
With Range("M5:M" & eRow)
.FormulaR1C1 = "=ABS(RC[-1])"
.Value = .Value
End With
Cells(eRow + 1, "M").FormulaR1C1 = _
"=SUM(R[-" & eRow - 4 & "]C:R[-1]C)"
End Sub

Hope this helps
Rowan

Jacob G wrote:
Hello All,
I am having the hardest time coming up with an answer to what is
seemingly the easiest function of Excel. I have Loop Until Statement
that gets me the absolute value of a column and lists it into the next
column over. I have it looped because there is no definitive ending
point of how many rows there may be, one day 5, the next day 205. I can
get that done, but what seems the most simple, I just can't figure out.
How do I code it to Sum the total of the 2nd column? Here is the code I
have:

Sub Count_Cases()
Range("M5").Select
Do
ActiveCell.Value = Abs(ActiveCell.Offset(0, -1))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
<<<Here is where I need it to Total all of the values in this column
starting with "M5" through: ActiveCell.Offset (-1,0){This is where I get
confused, I can't define the Active Cell and use FormulaR1C1, or can I?}
End Sub

Any ideas would be greatly appreciated.

Jacob

Any Help with a Simple Function?
 

You're welcome.

Jacob G wrote:
Rowan,
This worked Perfectly, Thank You Very Much!!

Any Help with a Simple Function?
 

"Jacob G" wrote in message
...
The last statement below, One Line above the End Sub is where I can't get it
to work.

Sub Count_Cases1()
Range("M5").Select
Do
ActiveCell.Value = Abs(Selection.Offset(0, -1))
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(Selection.Offset(0, -1))
ActiveCell = Sum (Range("M5", ActiveCell.Offset(-1,0)))
End Sub

Hi Jacob
you have already soleved your problem by Rowan. so, you may don't need this any
more.

But how about changing above the line
ActiveCell = Sum (Range("M5", ActiveCell.Offset(-1,0)))
to
ActiveCell = Application.Sum(Range("M5", ActiveCell.Offset(-1, 0)))
or
ActiveCell.Formula = "=sum(M5:" & ActiveCell.Offset(-1, 0).Address(False, False) &
")"

keizi