LinkBack URL
About LinkBacks
Hi all.
How would I go about writing a loop that can run down rows until it hits, for ex 1/1/2010 (dates are in column A).
Also, would I be able to make a loop run for 30 rows and perform some calculations with two different columns?
Hi all.
How would I go about writing a loop that can run down rows until it hits, for ex 1/1/2010 (dates are in column A).
Also, would I be able to make a loop run for 30 rows and perform some calculations with two different columns?
So I would like a loop to run (either for a predefined number of rows or until it hits a certain date - in column A) ... and do the following:
There will be positive and negative values in Column E. So every time a loop sees a positive value, it should add it all up...and when it sees a negative value it should add all of those up as well, and then put those two numbers wherever on the sheet.
So the input is a bunch of negative and positive numbers, and the output is two numbers: a sum of positive, and sum of negative numbers from that bunch.
Is this possible?
You don't need a macro for that. A formula should probably be able to do that.
Click GO ADVANCED and use the paperclip icon to post up your workbook. Point out where you want the SUM of positive and SUM of negative numbers to appear.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
okay, i uploaded the file and instructions are on the same sheet.
i am looking for a VBA code as it will be easier to play around with. you will see what i mean when u see the workbook.
thank you in advance!!
This should show you what I mean. This isn't hard at all. Not sure if I summed the right column, but you can adjust that.
Or, in N3, =SUMIF(INDEX(H:H, N$2):INDEX(H:H, N$2+N$3-1), ">0")
In N4: =SUMIF(INDEX(H:H, N$2):INDEX(H:H, N$2+N$3-1), "<0")
Entia non sunt multiplicanda sine necessitate
The first line works, for N3. The second for N4 doesn't.
Is there any way we can shorten the code so we can just say "30", instead of telling it to sum up rows 1 to 31 ?
Thank you for your help! This is exactly what I was looking for!
can this be done in VBA by any chance? I think it would be a lot easier as you can have a textbox to enter value (for however many rows you want the loop to run)
any thoughts?
This is what I have for VBA code. It doesn't work though. Any help would be greatly appreciated guys.
Please Login or Register to view this content.
Okay, I've done more testing and apparently I can't define Range asbut asPlease Login or Register to view this content.And I don't think i am setting the change.Value correctly. I am trying to tell it to be greater than 0, but I don't think it is correct.Please Login or Register to view this content.
Also, how would I be able to tell the loop to stop after lets say 30 rows? 60? Instead of running through the whole sheet.
Last edited by losmi8; 02-06-2010 at 09:23 PM.
And here is the file I am working with. Then you'll know what is what in my code.
Okay, I got the code to work but it is not what I want. So here is the working code:
"side" is there just so i can control for how many rows the code runs. Is that the correct way of doing it? It seems to work...Please Login or Register to view this content.
Now, the code works ONLY if there is a "75" somewhere in Column H (or any other whole number i enter)
However, to make this code perfect, I would want it to, instead of "75", look for values that are GREATER than 0.
Any help would be appreciated guys. Thanks!
Last edited by losmi8; 02-06-2010 at 09:22 PM.
okay, i solved the issue. there was some text in the first row that didn't allow me to do >=0
thanks for all that helped!
guys one more thing, how can i get the number of the positive days the code adds up as volume?
i tell it to run for 30 rows....and in those 30 rows, lets say there were 12 positive values, which the code adds up....but how do i get that 12? i want that to be stored on the sheet somewhere. i dont want to manually count.
i need that 12 because i want to calculate the average postive value.
...how can i get the number of the positive days the code adds up as volume?
Please Login or Register to view this content.
Ben Van Johnson
Here's the code to include the counts. Note that I don't know where you want them place so you will have to edit the code where indicated
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks