How do I change the range in this code.

[RAJESH1960]

'for' Loop @Fluff need your help to edit this..
This code was shared by Fluff last year which worked correctly in the previous project. At first the data extracted was filled in around 600 rows but when the value of the amount to be extracted has increased, it is not possible to get the data in 600 rows. Hence the formulas in rows after the 600th row have been shifted to the top so that I can get unlimited rows to fill the data.
If the number of rows is avoided and changed to unlimited rows it will be really helpful in future.
I am trying this code by changing the range in the code by editing it. But I am getting a run time error 1004 at this line. Can someone please help me to correct it.? I want to add this code at the end of the existing code.

         Cells(2000, .Offset(, -14).Column).End(xlUp).Offset(1).Resize(2).Value = .Offset(.Count - 2).Resize(2).Value

Link to the file is below
Shift amounts.xlsm

 

[johnnyL]

Glad to help.

 

[johnnyL]

You probably shouldn't mark the solution to a post that links a file that will only last a couple of weeks.

 

[johnnyL]

Was the code fast enough for you?

 

[RAJESH1960]

I forgot to mention that. The old code used to take 5-6 minutes to complete and display the result. This code takes less than a minute no matter how big the target amount is.
You once mentioned in a post how to check the time taken by the code to run. What keys do I press to view that in the editor window.?

 

[RAJESH1960]

I just remembered that I have to add some lines in the code to check the time and press control G. I will check that out.

 

[johnnyL]

CTRL+G in the VBE window to display the 'Immediate' window, if it is not already displayed.

But you also have to have timing code in the script for it to display there.

 

[johnnyL]

At the beginning of your code:

'
    Dim StartTime                   As Double
    StartTime = Timer

And right before the end of your code:

'
    Debug.Print "Time to complete = " & Timer - StartTime & " seconds."

 

[RAJESH1960]

Time to complete = 3.4453125 seconds. for huge amounts
Time to complete = 1.87109375 seconds for small amounts

 

[johnnyL]

Much better than 5 minutes. LMAO

 

[RAJESH1960]

Much better than 5 minutes. LMAO

Better is a small word. It is just F A N T A S T I C.