Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any Help with a Simple Function?
You're welcome.
Jacob G wrote: Rowan, This worked Perfectly, Thank You Very Much!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
COM Add-in - Simple Function | Excel Programming | |||
simple if then function | Excel Worksheet Functions | |||
Simple Function Help ~ Please | Excel Worksheet Functions | |||
A simple function | Excel Programming |