jazznaura
08-22-2008, 02:41 PM
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
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