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