Speeding up a loop

[gen20]

Hello!

I've been lurking these forums for quite some time, and found so many helpful. Now I'd like to ask for some help too.

I have a nasty array formula in cell c6, with matching time and different criteria, at first I autofilled it on my sheet which is around 5000 rows at max, but it took too much time for calculating, (I also copied values on them after calculation). I thought with a looping it would be quicker, which is slightly faster then autofilling the whole column, but I find calculating and copying cell by cell is too slow. Could you recommend any other workarounds or changes to my loop?

Thanks alot in advance.

my loop:

Sub calculate()
Dim lastrow As Long
Dim rowcurr As String
Dim rownext As String
Dim rowstart As String
Dim rowif As String
lastrow = Cells(Rows.Count, "d").End(xlUp).Row - 6

Range("c6").Select
rowcurr = ActiveCell.Address(0, 0)
rowstart = rowcurr
Range(rowcurr).Select
Selection.Offset(1).Select
rownext = ActiveCell.Address(0, 0)

Application.Calculation = xlManual
For x = 1 To lastrow
Range(rowcurr).Select
Selection.AutoFill Destination:=Range(rowcurr, rownext)
Calculate
Range(rownext).Select
Selection.Offset(-1).Select
rowif = ActiveCell.Address(0, 0)
If rowif = rowstart Then GoTo step Else
ActiveCell.Copy
Selection.PasteSpecial xlPasteValues
step:
Range(rownext).Select
rowcurr = ActiveCell.Address(0, 0)
Selection.Offset(1).Select
rownext = ActiveCell.Address(0, 0)
Next
Application.Calculation = Automatic
End Sub

 

[RedBeard]

Since you using select inside the loop, that's going to be very costly in terms of performance. Can't you simply work with the loop increment?

 

[ajamess]

Can you post what your array formula is? I think there's a good chance it might be faster and easier to do the calculation that formula is doing in the loop, instead of filling and copy/pasting as values.

 

[MARK858]

Or just applying the formula directly to the full range rather than looping will also be quicker.
Again though you need to post your formula for the first cell.

 

[gen20]

Unfortunately I'm no longer at work, and I cannot post my array formula just yet, but it may be all wrong after all. I've reconstructed a sample table (look below). My formula displays the right channel group from column D to I. Main criterias are the channel, the date, and the closest time, as I'd like to pair the right channel groups to the actual data based upon the estimated times (which nearly always has a few minutes difference).

The main problem was, that different channel groups were assigned to certain channels at different times - I've highlighted these, as you see channel "CBA" could be either in maxi or maxi_2. With the pairing I would like to assign the right channel groups to the actual report. Other thing is, they may be more data in the actual report (meaning more was delivered than estimated, but it would just assign either channel group as it was a bonus, at these doesn't really matter where they go (either maxi or maxi_2)

The reason for blank cells and filling, is because the report is imported from another source and wouldn't want to bother with manual filling.

I'm open to any formula ideas and workarounds.