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
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
