coding to add values until a certain limit

**dan2010** · 08-16-2010, 01:16 AM

Hi,

i would like to know what is wrong with my coding. This coding is supposed to let excel list down all the items of which the total cost is within budget and paste these items on sheet2. the budget will be declared at cell E2 by user. the result of my coding gives me only the 1st item in sheet 2 although i set the budget to be 100.

sheet 1:
Location " risk value " "cost "
a 12 12
b 10 32
c 7 10
d 5 15

Please Login or Register  to view this content.

what if my cost and risk values are formulas based on other factors in sheet1. what adjustments do i have to make to allow excel to copy only the name, values of cost and risk values into sheet 2?

**royUK** · 08-16-2010, 01:23 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**dan2010** · 08-16-2010, 03:47 AM

i have uploaded an example. thanks.

**dan2010** · 08-16-2010, 03:54 AM

please ignore the previous attachment.

**Andrew-R** · 08-16-2010, 04:40 AM

OK, you've got numerous problems with your code.

In no particular order:

1. The syntax of your For Next loop is wrong, it's For i=0 to 1000000, not for i=0 to i=100000 - that is a boolean statement which evalutes to 0, so your loop says For i=0 to 0, which is why it only copies the first row.

2. You don't need to manually increment i - that's what the loop does.

3. A for next loop is the wrong tool for the job anyway, you'd be better off manually incrementing i and having the whole lot in a While sum=<budget ... wend loop.

**dan2010** · 08-16-2010, 04:50 AM

i see. while,wend works well. thanks for ur help

btw do u know what code can i use to show the values of "risk value" and "cost" from sheet 1 instead of copying the entire row? i am supposed to show only the location,risk value and cost in sheet 2. if i remove the values of other factors, "risk value" and "cost" will turn to 0.

**Andrew-R** · 08-16-2010, 04:56 AM

No problem...

Please Login or Register  to view this content.

Try that.

**dan2010** · 08-16-2010, 05:05 AM

its perfect

btw using this coding will result in the total cost of items more than budget. so i came up with this "sheet2.cells(i+2,cellloop).delete" to delete the last item. however, it is not working. do u know what is wrong?

**Andrew-R** · 08-16-2010, 05:11 AM

Rather than messing around with deleting rows you'd be better off with:

Please Login or Register  to view this content.

That should avoid the problem of going over budget.

**dan2010** · 08-16-2010, 05:18 AM

all right. thanks alot

coding to add values until a certain limit

LinkBack

Thread Tools

Rate This Thread

Display

coding to add values until a certain limit

Re: coding to add values until a certain limit

Re: coding to add values until a certain limit

Re: coding to add values until a certain limit

Re: coding to add values until a certain limit

Re: coding to add values until a certain limit

Re: coding to add values until a certain limit

Re: coding to add values until a certain limit

Re: coding to add values until a certain limit

Re: coding to add values until a certain limit

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions