Summing in VBA

[Vlad21]

Hi guys,


So I've been using excel for about a year now and this is my first attempt at creating a macro. Unsurprisingly it's not working out to well.


I'm trying to create a macro that will sum our customers demand for a certain part until it's equal to our stock value and then return the date that our stock will last till.
Here's an example of the way my table is laid out:

Part Number	Description	PO	Date Needed	Delivery Date	QTY Needed	Stock QTY	Production QTY	First Short Date	Planning Start Date	Past Due	7/6/2014	7/13/2014	7/20/2014
14926	PIN	PO-12846			14	13	0	10/12/2014		1	0	1	1
15242	CUP	PO-89647			3	0	10	Planning Start Date		1	0	1	1
2081	TENSION ROD	PO-36987			11	13	50	10/12/2014		1	0	1	1
2022	SHAFT	PO-16487			4	15	0			1	2	1	0
2050	CELL	PO-69587			5	1	20			2	0	1	2
2138	GEAR	PO-24761			4	11	0			1	2	1	0
2148	FILTER	PO-64975			14	40	0			1	6	3	0
2001	BACK SUPPORT	PO-36548			11	0	20			9	0	1	1
2431	WEB	PO-54877			9	0	15			7	1	1	0

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

​

Don't mind the empty cells (Date Needed, Delivery Date and Planning Start date) other info will go here later on. Right now I'm trying to fill the first short date column with the date our stock will last till. Here is the VBA code I have right now:

Sub first_short_date()
Dim stock As Integer
Dim row_count As Integer
Dim column_count As Integer
Dim demand_sum As Integer



row_count = 2 'Starting point of data under title

Do Until Cells(row_count, 1).Value = "" 'Stops when data ends

stock = Cells(row_count, 7) 'Stock data located in Column B
column_count = 11 'Beginning of demand data
demand_sum = 0 'Reset demand

Do Until demand_sum = stock 'Stops when stock reaches demand

demand_sum = Application.Sum(Range(Cells(row_count, 11), Cells(row_count, column_count))) 'Progressivly sums demand
column_count = column_count + 1

Loop

Cells(row_count, 9) = Cells(1, column_count - 1).Value 'Returns the corresponding date to when demand=stock
row_count = row_count + 1

Loop

End Sub


As you can see from the table above this works for the first 3 rows (I know that it's not returning the date for row 2 but this is because the stock is zero. I plan on adding an if statement to fix this later). When it gets to the 4th row it returns, run time error "1004": Application-defined or Object-defined error.
I ran the code line by line with the debug and it seems to get stock in an infinite loop at this part:

Do Until demand_sum = stock 'Stops when stock reaches demand

demand_sum = Application.Sum(Range(Cells(row_count, 11), Cells(row_count, column_count))) 'Progressivly sums demand
column_count = column_count + 1

Loop

At first I thought that maybe my stock was exceeding the demand but this table continues on for a while and the demand is much greater then the stock.

If you guys can offer any advice on this I would greatly appreciate it.

 

[Andrew Poulsom]

Welcome to MrExcel.

Maybe you need:

Do Until demand_sum >= stock

 

[Vlad21]

Ya that works, thank you.
I've been pulling my hair out trying to figure this thing out.
I'm new to programming in general so I tend to miss things like that, thank you again.