Excel Crashes when I try to run VBA marco

aguti1899

New Member
Joined
Oct 9, 2015
Messages
1
So I'm new to VBA and am having problems with this one marco. I have written it in two different ways to get it to work and I cant. Both the codes work but just take about 10 mins and sometimes it crashes. I am trying to identify the last time an item was moved in our inventory by using the transaction data of the last 2 years. For reference FinalRowInput is about 21,000 and FinalRowOutput is 102. These are the two codes:

Sub DateSearch()
Dim NewDate As Date
Dim OldDate As Date
Dim SKU As String
Dim i As Integer
Dim j As Integer

Sheets("Output").Range("E2:E500").ClearContents
FinalRowInput = Sheets("Input").Range("A40000").End(xlUp).Row
FinalRowOutput = Sheets("Output").Range("A100000").End(xlUp).Row

j = 268
Do While j < FinalRowInput + 1
Sheets("Input").Select
SKU = Cells(j, 11)
MoveDate = Cells(j, 1).Value

i = 2
Do While i < FinalRowOutput + 1
Sheets("Output").Select
If SKU = Cells(i, 1) Then
If Cells(i, 5) = 0 Then
Cells(i, 5) = MoveDate
i = FinalRowOutput
End If
If MoveDate > Cells(i, 5) Then
Cells(i, 5) = MoveDate
i = FinalRowOutput
End If
End If
i = i + 1
Loop
j = j + 1
Loop
End Sub


and

Sub DateSearch()
Dim NewDate As Date
Dim OldDate As Date
Dim SKU As String
Dim i As Integer
Dim j As Integer

Sheets("Output").Range("E2:E50").ClearContents
FinalRowInput = Sheets("Input").Range("A40000").End(xlUp).Row
FinalRowOutput = Sheets("Output").Range("A100000").End(xlUp).Row

j = 2
Do While j < FinalRowOutput + 1
Sheets("Output").Select
SKU = Cells(j, 1)
OldDate = 1 / 12 / 1999

i = 2
Do While i < FinalRowInput + 1

Sheets("Input").Select
If SKU = Cells(i, 11) Then
NewDate = Cells(i, 1).Value
If NewDate > OldDate Then
Sheets("Output").Select
Cells(j, 5) = NewDate
OldDate = NewDate
End If
End If
i = i + 1
Loop
j = j + 1
Loop
End Sub
 
Last edited:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

nils7

New Member
Joined
Oct 7, 2012
Messages
42
Hi Aguti,

Without looking at your code, please check that you have the latest Service Pack for your exce version. I have a macro that is also manipulating a large amount of data. Ot runs fine on users PC if they have Excel 2010 SP1 or SP2 installed, but crashes Excel if they haven't got either of the Service Packs installed.

I don't know which version of Excel you are running, but have a look at under the menu "File"-"Help". This will show your version number. E.g. Excel 2010 is 14.0, but then the next 4 digits show which Servie Pack you have installed.

This might not be the reason for your crash, but it is worth having a look and making sure you have the latest SP installed as it can cause issues.

Cheers,

Nils
 

Forum statistics

Threads
1,137,353
Messages
5,680,998
Members
419,948
Latest member
Sbakker1

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
Top