Summing in VBA

Status
Not open for further replies.
V

Vlad21

New Member
Joined
Jul 10, 2014
Messages
11
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:

[TABLE="width: 1229"]
<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD="align: center"]Part Number[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]PO[/TD]
[TD="align: center"] Date Needed[/TD]
[TD="align: center"] Delivery Date[/TD]
[TD="align: center"]QTY Needed[/TD]
[TD="align: center"]Stock QTY[/TD]
[TD="align: center"]Production QTY[/TD]
[TD="align: center"]First Short Date[/TD]
[TD="align: center"]Planning Start Date[/TD]
[TD="align: center"]Past Due[/TD]
[TD="align: center"]7/6/2014[/TD]
[TD="align: center"]7/13/2014[/TD]
[TD="align: center"]7/20/2014[/TD]
[/TR]
[TR]
[TD="align: center"]14926[/TD]
[TD="align: center"]PIN [/TD]
[TD="align: center"]PO-12846[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10/12/2014[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]15242[/TD]
[TD="align: center"]CUP[/TD]
[TD="align: center"]PO-89647[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Planning Start Date[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2081[/TD]
[TD="align: center"]TENSION ROD[/TD]
[TD="align: center"]PO-36987[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]10/12/2014[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2022[/TD]
[TD="align: center"]SHAFT[/TD]
[TD="align: center"]PO-16487[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]2050[/TD]
[TD="align: center"]CELL[/TD]
[TD="align: center"]PO-69587[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]2138[/TD]
[TD="align: center"]GEAR [/TD]
[TD="align: center"]PO-24761[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]2148[/TD]
[TD="align: center"]FILTER [/TD]
[TD="align: center"]PO-64975[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]2001[/TD]
[TD="align: center"]BACK SUPPORT [/TD]
[TD="align: center"]PO-36548[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2431[/TD]
[TD="align: center"]WEB[/TD]
[TD="align: center"]PO-54877[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

​

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.
 
Sort by date Sort by votes
Status
Not open for further replies.

Similar threads

K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
1K
Alex Blakenburg
A
P
  • Question Question
How to filter column with same unique match name and to obtain their average values
2
Replies
18
Views
484
pankajgrover
P
J
  • Solved
VBA code - Copy pasting cells from sheet1 to sheet2
2 3
Replies
24
Views
2K
J S A
J
A
  • Question Question
Bold and color text
Replies
4
Views
86
Kaper
Kaper
I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
665
Fuji
F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top