Excel Crashes when I try to run VBA marco

[aguti1899]

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

 

[nils7]

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