OK...
Let's see if I can get this right.
The numbers you want to add to get to 5000 are in column "I".
the date(s) you want to add will be in column "M".
The "starting" date you will put in "A1"
you want to DECREASE the date by (1) day...
But ONLY on weekdays...
try this:
Option Explicit
Sub Macro1()
Dim I, Ttl, Col_M, Col_I, Date_Flag
Dim MaxLoop, LoopCnt, blankcnt, MAXrow
MAXrow = 1500
Col_I = 9
Col_M = 13
MaxLoop = 1000
Date_Flag = Range("A1").Value + 1
Range("M2:M65000").ClearContents
For LoopCnt = 1 To MaxLoop
If (Application.WorksheetFunction.CountBlank(Range("M2:M" & MAXrow)) <=
0) Then Exit For
Date_Flag = Date_Flag - 1
If (Weekday(Date_Flag, vbMonday) > vbFriday) Then Date_Flag = Date_Flag
- 2
Application.StatusBar = Date_Flag
Ttl = 0
For I = 2 To MAXrow
If (Cells(I, Col_I).Value = "") Then Exit For
If (Cells(I, Col_M).Value & "X" = "X") Then
If Ttl + Cells(I, Col_I) <= 5000 Then
Ttl = Ttl + Cells(I, Col_I)
Cells(I, Col_M) = Date_Flag
If Ttl = 5000 Then Exit For
End If
End If
Next I
Next LoopCnt
MsgBox Application.WorksheetFunction.CountBlank(Range("M2:M1500")) & "
blanks"
Application.StatusBar = False
End Sub
________________________________
From: wongsol <wong...@rogers.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Monday, June 22, 2009 11:18:45 AM
Subject: $$Excel-Macros$$ Re: Loop through count and add date
Fabio...thanks but that didn't work very well.
Paul..your code works really well, but I guess what I'm asking for
help with at this point is to take the first instance of 5000 or less,
and add the date indicated above in an adjacent cell somewhere (which,
again, your code does nicely), then loop back, ignore the "X"
indicated cell and count only those with blank cell (in this case cell
B by your code) and count; indicate with "X" and then insert date with
=MIN(c2:c10000)-1, this would be WORKDAY only.
On Jun 19, 1:31 pm, wongsol <wong...@rogers.com> wrote:
> 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 -- 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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
