Can i use arrays here?

[jazznaura]

Hi all,
I?ve been using the code below for a while and after reading many post on this forum, I?m thinking this could be faster if I us arrays. Unfortunately I know nothing about them and don?t know where to start. The code is only a small portion of full code which takes about 15 mins to run, as it contains 10 similar loops.
Could someone have a look and see if it is suitable to use arrays in instead and possibly give me a point in the right direction. I would be grateful if you could explain things in simple terms, as I?m having real trouble understanding arrays and how and what I can use them for.

Thanks,
Sub FindANDCopy() ' Search OR15
Dim DAT As Range, C As Range, WSDATA As Worksheet, WSOR15 As Worksheet
Set WSDATA = Worksheets("DATA"): Set WSOR15 = Worksheets("OR15")
Sheets("DATA").Activate
Application.ScreenUpdating = False
' Read data
For Each C In WSDATA.Range("B2:B40000").Cells
If (C.Value) > 0 Then
Set DAT = WSOR15.Range("B1:B5000").Cells.Find(What:=C.Value, LookIn:=xlValues, LookAt:=xlWhole)
On Error Resume Next
Set DAT = DAT.Offset(, 1)
' Write MER code
If Not DAT Is Nothing Then
C.Offset(0, 1).Value = DAT.Value
C.Offset(0, 4).Value = DAT.Offset(, 1).Value
C.Offset(0, 3).Value = DAT.Offset(, 2).Value
End If
End If
Set DAT = Nothing
Next C
End sub

[mdmackillop]

Try a different methodology

Sub FindANDCopy() ' Search OR15

Dim WSDATA As Worksheet
Dim WSOR15 As Worksheet

Set WSDATA = Worksheets("DATA")
Set WSOR15 = Worksheets("OR15")

Application.ScreenUpdating = False

WSDATA.Range("C2:C40000").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC2,OR15!R1C2:R4000C5,2,FALSE)),"""",VLOOKUP(RC2,OR15!R1C2:R4000C5,2,FALSE))"

WSDATA.Range("F2:F40000").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC2,OR15!R1C2:R4000C5,3,FALSE)),"""",VLOOKUP(RC2,OR15!R1C2:R4000C5,3,FALSE))"

WSDATA.Range("D2:D40000").FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC2,OR15!R1C2:R4000C5,4,FALSE)),"""",VLOOKUP(RC2,OR15!R1C2:R4000C5,4,FALSE))"

With WSDATA.Range("C2:F40000")
.Copy
.PasteSpecial xlValues
End With
Application.ScreenUpdating = True
End Sub

[Kenneth Hobs]

Your routines should not take that long. You may need to set the calculation mode to manual.

You can put code below Test in a module in the use them as I did in Test.
Sub Test
SpeedOn
'your code here
SpeedOff
End Sub

Public glb_origCalculationMode As Integer

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub

[jazznaura]

thanks guys,
i'll try both of the above.
i am interested in learning about arrays and if anyone has any links to some good sites please let me know.

thanks again.