Speeding up a loop

gen20

New Member
Joined
Apr 19, 2017
Messages
3
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

Well-known Member
Joined
May 16, 2015
Messages
858
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

Board Regular
Joined
Sep 13, 2016
Messages
92
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,491
Office Version
365, 2010
Platform
Windows, Mobile
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

New Member
Joined
Apr 19, 2017
Messages
3
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.

 

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top