My proposal, although without an example of data it is difficult to find specific solutions:
1. Early binding could be used
2. You could improve the addressing of objects a bit - standardize them a bit
3. Are cells "A3" and "C25" involved in calculating some values "on the fly" for formulas in other cells ?
4. Data could be loaded from a spreadsheet to memory - calculations would be faster - but see point 3, which complicates the matter
5. You can divide the "If" conditions into two separate conditions, it should work faster
For example:
Sub M_2()
Dim wsXyz As Excel.Worksheet, awWs2 As Excel.Worksheet, awWsAbc As Excel.Worksheet
Dim wsXyzA1A60 As Excel.Range, awWsAbcA3 As Excel.Range, awWsAbcC25 As Excel.Range
Dim rslt As Excel.Range
Dim i&
Set wsXyz = ThisWorkbook.Worksheets("XYZ")
Set wsXyzA1A60 = wsXyz.Range("A1:A60")
With ActiveWorkbook
Set awWs2 = .Worksheets(2)
Set awWsAbc = .Worksheets("ABC")
End With
Set awWsAbcA3 = awWsAbc.Range("A3")
Set awWsAbcC25 = awWsAbc.Range("C25")
Fees:
For i = 1 To 80
If awWs2.Cells(1, i).Value = "GRAND TOTAL" Then
If awWs2.Cells(7, i).Value Like "US*" Then
awWsAbcC25.Value = awWs2.Cells(44, i).Value
'wsXyzA1A60.Find(awWsAbcA3.Value).Offset(0, 2).Value = "USD"
'wsXyzA1A60.Find(awWsAbcA3.Value).Offset(0, 6).Value = Now
Set rslt = wsXyzA1A60.Find(awWsAbcA3.Value, wsXyzA1A60.Cells(60), , xlWhole, xlByRows)
If Not rslt Is Nothing Then
With rslt
.Range(.Offset(0, 2), .Offset(0, 6)).Value = Array("USD", , , , Now)
End With
Set rslt = Nothing
End If
End If
End If
Next
End Sub
Bookmarks