My Code crashes Excel & VBA (suggestions?)

[sd1fa53s4fa5s65d]

Moshi Moshi

I am very new to excel. I learned it because I suddenly was given a large amount of data to organize. Unfortunately, I cannot figure out a way to make the code below run without crashing after spitting out a few solutions. By "crashing" I mean I am forced to restart Excel and VBA because both programs stops responding. I do not know why it is crashing, but I am thinking it is because I vba cannot process the task I am giving it.

I am able to complete what I want to accomplish by manually changing numbers in each "For" loop. If I were to allow all loops to run, the program will crash.

Any feedback will be helpful.

Happy Friday


Sub Multiply()


'Total
Dim C, B As Integer

'COMBINED Row Number
Dim X As Integer
X = 2

'Months
For Q = 1 To 12

If Q = 1 Then
P = 39

ElseIf Q = 3 Then
P = 39

ElseIf Q = 5 Then
P = 39

ElseIf Q = 7 Then
P = 39

ElseIf Q = 8 Then
P = 39

ElseIf Q = 10 Then
P = 39

ElseIf Q = 12 Then
P = 39

ElseIf Q = 2 Then
P = 36

Else: P = 38

End If

'however long the rows are in each MONTH
For A = 8 To P

'Sheet 13 loop for 105 times
For Y = 2 To 105

'Add up one total in each month
For Z = 7 To 1600

'Finding Totals for each item
If Sheets(13).Cells(Y, 1) = Sheets(Q).Cells(Z, 6) Then
B = Sheets(Q).Cells(Z, A) * Sheets(Q).Cells(Z, 5)
Else: B = 0
End If

'Add Totals
C = C + B

Next Z

'Prints Totals
Sheets(13).Cells(Y, X) = C

'Refresh Totals
C = 0

Next Y

X = X + 1

Next A

Next Q

End Sub

 

[Legacy 316950]

Hi,

I am new on VBA also, but these "for" inside other "for's" and one of them goes until 1600 it's not good. I did'nt understand really well what you want to do. Could you show an example?

 

[sd1fa53s4fa5s65d]

I appreciate your reply. Is it possible to overload VBA? If so, is there a way around it?

This is what I'm trying to accomplish:

• I have 13 tabs, 1 for each month and one to combine the information into
• Each month contains a 1 column with a product description (1600 lines of 108 different types of product with many duplicates), 1 column with the quantity of product in that certain package, and one column for each day of the month with how many of those products sold
• I want to combine all 12 months of those products to 1 tabs with 365 columns while aggregating the 1600 different types of products ordered into 108 rows

Does this add some clarification?

 

[Legacy 316950]

I don't know if it's overload but you lost performance. take a look in my thread http://www.mrexcel.com/forum/excel-questions/836664-problems-loop-paste.html#post4079513
i had a problem with loop also. Now i got what you want.
Unfortunately I cant' figure out a code to solve your problem, i'm starting also.
http://www.mrexcel.com/forum/excel-questions/836664-problems-loop-paste.html#post4079513