How to reduce time for below mentioned loop

[ThrottleWorks]

Hi,

I am running below mentioned loop in my code.

The loop takes around 1-2 minutes to run. Speed depends on the system speed of that particular time.

Is there better option to reduce the time required or can the below mentioned code be better.

Please help. I will try to explain what the code does below. Sorry for not uploading sample file.

The range Rng5 is in Sheet1.
From Sheet 1 I have defined non - blank cells of Column A as Rng5.

The loop runs for each non blank value from Column A.
Each value from the range is searched in Sheet 2.

Once macro finds the value it copies Offset(0, -6).value from the active cell of Sheet 2 and paste it in Sheet 1.

Prior to running the macro Sheet 2 is active where records are to be searched.

Once again sorry for not uploading file.

PS - The macro took 1 minute for searching 25 records.

For Each rn In Rng5
        Range("A1").Select
        Cells.Find(what:=rn, After:=ActiveCell, LookIn:= _
        xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, -6).Copy Destination:=rn.Offset(0, 6)
Next rn

 

[Deepak]

Hi,

I don't know it will speed up or not but i like that way.

Why u are looking in xlFormulas ?

For Each rn In Rng5
    rn.Offset(0, 6).Value = Sheet2.Cells.Find(what:=rn, LookIn:= _
        xlFormulas, lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False).Offset(0, -6).Value
Next rn

 

[ThrottleWorks]

Hi @Deepak Sir, thanks for the help.

To be honest I have to check, I recorded the "Find" code and edited reference part...

 

[p45cal]

Why u are looking in xlFormulas ?

If there are no formulas, .Find will look in the values, but much more importantly, using xlFormulas will search hidden rows too, whereas xlValues will only search among non-hidden rows (eg. filtered data).

A considerable time saving will probably be achieved by searching only the areas you expect to find rn. Currently you're searching the whole sheet:
.cells.find(…
but it's obvious that when you do find it that you want to copy cells 6 columns offset to the left - so I'd guess that you would want a result to be found among those 6 columns.
I expect that you only want to search one column for rn, so you could use:
.columns("G").find(…

Better still, a single column is still an awful lot of cells to search in versions of Excel after 2007, you can narrow it down further:

Set RangeToSearch = intersect(sheet2.usedrange,sheet2.columns("G"))
RangeToSearch.find(…

 

[Deepak]

If there are no formulas, .Find will look in the values, but much more importantly, using xlFormulas will search hidden rows too, whereas xlValues will only search among non-hidden rows (eg. filtered data).

I know that!!
I asked as if there not an important is to use xlFormulas then simply use filter and copy-paste instead of loop

 

[ThrottleWorks]

Hi @p45cal Sir, thanks for the help. Will share the results.

Hi @Deepak Sir, I have confusion about using filter rather than loop for the above code. Will get back with details.