Looping through a range is very slow compared to looping through an array.
Even though the range will has to be assigned to the array and vice-versa
after doing
the manipulations it will still win hands down.
Haven't compared with a sheet formula approach, but my guess is that the
array method will win.

Just compare TestRange and TestArray:

Option Explicit
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub TestRange()

Dim i As Long
Dim lastRow As Long
Dim oldWs As Worksheet
Dim newWs As Worksheet

Set oldWs = Sheets(1)
Set newWs = Sheets(2)

lastRow = 10000

StartSW

For i = 2 To lastRow
If (oldWs.Cells(i, 1).Value = "yes") Then
newWs.Cells(i, 1).Value = 1
Else
newWs.Cells(i, 1).Value = 0
End If
Next i

StopSW , "doing range"

End Sub

Sub TestArray()

Dim i As Long
Dim lastRow As Long
Dim arr1
Dim arr2
Dim oldWs As Worksheet
Dim newWs As Worksheet

Set oldWs = Sheets(1)
Set newWs = Sheets(2)

lastRow = 10000

StartSW

With oldWs
arr1 = Range(.Cells(2, 1), .Cells(lastRow, 1))
End With

ReDim arr2(1 To UBound(arr1), 1 To 1)

For i = 1 To UBound(arr1)
If (arr1(i, 1) = "yes") Then
arr2(i, 1) = 1
Else
arr2(i, 1) = 0
End If
Next i

With newWs
Range(.Cells(2, 1), .Cells(lastRow, 1)) = arr2
End With

StopSW , "doing array"

End Sub

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Function StopSW(Optional bMsgBox As Boolean = True, _
Optional vMessage As Variant, _
Optional lMinimumTimeToShow As Long = -1) As Variant

Dim lTime As Long

lTime = timeGetTime() - lStartTime

If lTime lMinimumTimeToShow Then
If IsMissing(vMessage) Then
StopSW = lTime
Else
StopSW = lTime & " - " & vMessage
End If
End If

If bMsgBox Then
If lTime lMinimumTimeToShow Then
MsgBox "Done in " & lTime & " msecs", , vMessage
End If
End If

End Function


RBS


"Lars Uffmann" wrote in message
...
just did a small benchmark myself with a nested if-loop as function and
did the same operation 100 times on 6321 lines:
Method 1: 23 seconds
Method 2: 34 seconds

I guess there is quite some optimisation in Excel Formula and Copy
operations... Let's see how it fares vs. my loop when I need to calculate
more fields per row :)