MDW,

This is from the Excel Developer's Kit:

Declare Once, Use Many Times
If you are going to use an object more than once, declare and use object
variables rather than using a fully qualified object reference each time.
For instance, in the following example the FillCells2 procedure runs
approximately three times as fast as the FillCells1 procedure. This is
because the VBA engine doesn't have to resolve the entire object reference
each time it runs through the loop.

Example removed by me...

Anyway, the upshot of the above is that you could try changing your code to:

Dim oRange As Range
Set objOther = sht2.Range("A4:H850")
Set oRange = shtTotals.Cells(8, 1)

For J = 1 To 12
With oRange.Cells(J, 1)
If .Value < "" Then
objOther.Replace .Value, .Offset(0,1).Value
End If
Next

HTH,
Bernie
MS Excel MVP


"MDW" wrote in message
...
Sorry for the cross post. My original was posted in the wrong NG.

I've got some code that processes a couple hundred Excel workbooks. There
are two sheets. On the first sheet, it lists a person's name and their SSN.
On the second sheet, it just lists names. I use this code to loop through
the name/SSN section on the first page (which will always be A8:B19, where
column A has the name and column B has the SSN) and replace the names with
the SSNs on the second.

Here is my code(shtTotals is the first sheet, sht2 is the second sheet):

Set objOther = sht2.Range("A4:H850")

For J = 8 To 19

If shtTotals.Cells(J, 1).Value < "" Then

objOther.Replace shtTotals.Cells(J, 1).Value, shtTotals.Cells(J,
2).Value

End If

Next

Although it does work, I've identified this block of code as the
slowest-running section. I'm not sure why. Am I coding something improperly?
Any assistance would be appreciated.

Of course, that code requires an "End With" toward the bottom....

For J = 1 To 12
With oRange.Cells(J, 1)
If .Value < "" Then
objOther.Replace .Value, .Offset(0,1).Value
End If
End With
Next

Sorry about that,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Anyway, the upshot of the above is that you could try changing your code
to:

Dim oRange As Range
Set objOther = sht2.Range("A4:H850")
Set oRange = shtTotals.Cells(8, 1)

For J = 1 To 12
With oRange.Cells(J, 1)
If .Value < "" Then
objOther.Replace .Value, .Offset(0,1).Value
End If
Next