the date is added; say in cell A1. This will be the date to work
backwards from; so if i wanted the first 5000 records to start on sept
15, then i would set A1= Sept 16...this would just be to start, then I
would change the date to be something like =MIN(B1:B100)-1 <this being
where you are inserting the dates in your code.
For the sake of argument. The column to count in the spreadsheet is
"I2:I1500". The column in which to insert the Date is "M2:M1500".
Where you could put the "X" indicator could be "N2:N1500". I tried to
play around with your code to do this, but I am newb and am getting
X's all over :P.
Again, thanks for your help thus far.
On Jun 19, 1:21 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> Please explain the date.
>
> is it there already? or added? added by? purpose?
>
> Paul
>
> ________________________________
> From: wongsol <wong...@rogers.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Friday, June 19, 2009 12:58:10 PM
> Subject: $$Excel-Macros$$ Re: Loop through count and add date
>
> Hi Paul...I did away with the sort in the macro and fixed the error (i
> use excel 2003) The other part worked great.
> i guess next would be to insert the date in the adjacent cell and then
> loop through those that do not contain "x" and do the same. (no idea
> how to do this)
>
> Thanks for your help thus far
>
> On Jun 19, 10:06 am, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>
>
> > I'm sorry, but there are too many confusing statements here...
> > I understand that you have a column of numbers and you
> > want to add them up until your reach (without exceeding) 5000.
> > What does "(doesn't matter which order)" mean?
> > meaning that the numbers are not sorted in any way?
> > or that you don't CARE if they are sorted?
> > And... it sounds like once you determine which numbers
> > are used to reach the 5000, you want to enter a DATE next to them.
> > What date? where does it come from?
>
> > And...
> > "working backwards from a predetermined date".
> > what does that mean? where does THIS "predetermined" date come from?
> > Do you mean SPECIFIED date?
>
> > I threw together a quick macro that did a PART of what you're looking for,
> > but I have no idea how close it is to what you want.
>
> > Sub Macro1()
> > Dim I, Ttl
> > Range("A1").Select
> > ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
> > ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
> > Key:=Range("A2:A1001" _
> > ), SortOn:=xlSortOnValues, Order:=xlDescending,
> > DataOption:=xlSortNormal
> > With ActiveWorkbook.Worksheets("Sheet1").Sort
> > .SetRange Range("A1:A10001")
> > .Header = xlYes
> > .MatchCase = False
> > .Orientation = xlTopToBottom
> > .SortMethod = xlPinYin
> > .Apply
> > End With
> > Range("B2:B65000").ClearContents
> > Ttl = 0
> > For I = 2 To 10001
> > If Ttl + Cells(I, 1) <= 5000 Then
> > Ttl = Ttl + Cells(I, 1)
> > Cells(I, 2) = "X"
> > If Ttl = 5000 Then Exit For
> > End If
> > Next I
> > MsgBox Ttl
> > End Sub
>
> > Paul
>
> > ________________________________
> > From: wongsol <wong...@rogers.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Thursday, June 18, 2009 2:54:03 PM
> > Subject: $$Excel-Macros$$ Loop through count and add date
>
> > Hello:
>
> > I'll try to explain as best I can.
>
> > I have a spreadsheet with a column that have numeric values. What I
> > need to do is loop through these values to a sum no greater than 5000
> > (doesn't matter in which order); the tricky part that I have no idea
> > where to start is to enter a date in the adjacent cell of all affected
> > cells that comprised this sum. This date would be working backwards
> > from a predetermined date; something like =MIN(range)-1.
>
> > So a column with values would have the following dates associated as
> > the sum is as close to 5000 but no more than 5000
>
> > 191 15-Sep
> > 235 15-Sep
> > 328 15-Sep
> > 534 15-Sep
> > 112 15-Sep
> > 155 15-Sep
> > 173 15-Sep
> > 203 15-Sep
> > 238 15-Sep
> > 1 15-Sep
> > 14 15-Sep
> > 57 15-Sep
> > 347 15-Sep
> > 48 15-Sep
> > 257 15-Sep
> > 301 15-Sep
> > 307 15-Sep
> > 513 15-Sep
> > 386 15-Sep
> > 401 15-Sep
> > 57 15-Sep
> > 150 14-Sep
> > 52 15-Sep
> > 187 14-Sep
> > 77 14-Sep
> > 82 15-Sep
> > 2 14-Sep
> > 156 14-Sep
> > 270 14-Sep
> > 474 14-Sep
> > 3 14-Sep
> > 156 14-Sep
> > 277 14-Sep
> > 280 14-Sep
> > 215 14-Sep
> > 445 14-Sep
> > 885 14-Sep
> > 457 14-Sep
> > 414 14-Sep
> > 277 14-Sep
>
> > ...any ideas?
>
> > TIA- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
