Column Loop, Formula and Wait

[GeeWhiz7]

Hi folks,

I had some help on this earlier from Joe4 but I have reached a sticking point again.

• I'm dealing with an internal company API that I get data from by using an excel add-in.
• I put a formula in the cell, it looks at the date and the account plus arguments in the formula and retrieves data right to the cell.
• Once the number of dates and accounts gets large (1000x2000 for example) something happens and the API data doesn't make it to excel. Maybe because its trying to refresh all those cells periodically.

My planned workaround is to go column by column in a loop, request the data and once it's in the column copy and paste as values to get rid of the formula/api call.

Feeling chuffed that it would work I ended up with the code below. But, it doesn't always work because the API is sometimes faster, sometimes slower. When it is being quick, the first Sub populates the column with the API formula, delays 5 seconds to let the API work and then cuts and pastes only the value before moving on and works great.

My problem is when the API is a little slower.

Is there any way to modify the code below to not just wait a certain amount of time, but to determine whether or not there is anything in the column and IF NOT, WAIT. If so, move on to the next column range.
I'd like to be able to let the application/code decide when the API formula call is done and shows a numeric value.

Any help is appreciated. Mini-sheet below and code although without the API you won't be able to call data.

Thanks!

Sub LevelDivisionRefMan()
'
    Dim rng As Range
    Dim y As Long
               
y = 2
    
    For Each rng In Range("B2:G2").Columns 'This actually can have almost a thousand columns'

    Range(Cells(2, y), Cells(1260, 2)).Formula2R1C1 = _
        "=DailyMarkAcct(R1C,RC1,Account(),ManufacRef())"
        
    Call DelayFiveSeconds
   
    Range(Cells(2, y), Cells(1260, 2)).Copy
    Range(Cells(2, y), Cells(1260, 2)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    y = y + 1
    
    Next rng

End Sub

Sub DelayFiveSeconds()

    Dim NowTick As Long
    Dim EndTick As Long

    NowTick = Now
    EndTick = Now + TimeValue("00:00:05")

    Do Until NowTick >= EndTick
        DoEvents
        NowTick = Now()
    Loop

End Sub

TestFillBFXcell.xlsb
	A	B	C	D	E	F	G
1	Date	L1Agg-1225833000	L1a-1225833000	L1b-1225833000	L1c-1225833000	L1d-1225833000	L1e-1225833000
2	5/23/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
3	5/20/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
4	5/19/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
5	5/18/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
6	5/17/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
7	5/16/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
8	5/13/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
9	5/12/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
10	5/11/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
11	5/10/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
12	5/9/2022	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
Sheet2

Cell Formulas
Range	Formula
B2:G12	B2	=DailyMarkAcct(B$1,$A2,Accout(),ManufacRef())