Loop through a big data table

[ISMI]

Hello gurus again,

I have one question regarding looping through the big data table. What will be the fastest method to put certain value (row3) in certain time interval (rows 1 and 2)? Time axes is on column E. Thing is that I tried Do While loop, and in case of 10000 rows and 100 columns It tooks few minutes to calculate. File example in attachment. Appriciated for your help like always.

[CheeseSandwich]

There will be a faster way but thought I would share what I had created in case it gets others creative juices flowing:

Sub test()
    Dim rCell As Range, lkpVal
    Dim lwrVal As Long, uprVal As Long
    Dim WriteVal As Double
    
    For Each rCell In Range("A6:D21").Cells
        lkpVal = Range("E" & rCell.Row)
        lwrVal = Cells(1, rCell.Column)
        uprVal = Cells(2, rCell.Column)
        WriteVal = Cells(3, rCell.Column)
        
        If lkpVal >= lwrVal And lkpVal <= uprVal Then
            rCell.Value = WriteVal
        End If
    Next rCell
End Sub

[CheeseSandwich]

May be a little faster if we don't declare the variables unless we need to:

Sub test()
    Dim rCell As Range, lkpVal
    Dim lwrVal As Long, uprVal As Long
    Dim WriteVal As Double, c As Long, r As Long
    
    For Each rCell In Range("A6:D10209").Cells
        If rCell.Row <> r Then
            lkpVal = Range("E" & rCell.Row)
        End If
        
        If rCell.Column <> c Then
            lwrVal = Cells(1, rCell.Column)
            uprVal = Cells(2, rCell.Column)
            WriteVal = Cells(3, rCell.Column)
        End If
        
        If lkpVal >= lwrVal And lkpVal <= uprVal Then
            rCell.Value = WriteVal
        End If
        
        c = rCell.Column
        r = rCell.Row
    Next rCell
End Sub

[ISMI]

Hello, it works but also took some time (few minutes for 56000 rows and 50 columns).

What if we will use offset function: so for each column jump directly to start cell eg A8735 (skip all previous rows) and to insert value until until A8740, then jump to column B and so on...? what you think?

[Sintek]

Pls supply a sample with a bigger data set i.e 1000 rows | 20 cols

[ISMI]

Hello my friend,
It is done, look in attached file. Time in column U

[Sintek]

For this to work...Put a Header in Column U

Sub J3v16()
Dim Data, Temp, X1, X2, i As Long, ii As Long
Data = Cells(1).CurrentRegion: ReDim Temp(1 To UBound(Data, 1), 1 To UBound(Data, 2))
For i = 1 To UBound(Data, 2) - 1
    X1 = Application.Match(Data(1, i), Application.Index(Data, , UBound(Data, 2)), 0)
    X2 = Application.Match(Data(2, i), Application.Index(Data, , UBound(Data, 2)), 0)
    For ii = X1 - 5 To X2 - 5: Temp(ii, i) = Data(3, i): Next ii
Next i
Range("A6").Resize(UBound(Data, 1), UBound(Data, 2) - 1) = Temp
End Sub

[Bo_Ry]

Please try

Sub noLoop()
Dim r&, c&, tm$, st$, en$, va$
With [a1].CurrentRegion
    r = .Rows.Count - 5
    c = .Columns.Count - 1
    tm = .Columns(.Columns.Count).Offset(5).Resize(r).Address
    st = .Rows(1).Resize(, c).Address
    en = .Rows(2).Resize(, c).Address
    va = .Rows(3).Resize(, c).Address
    .Offset(5).Resize(r, c).Value = Evaluate(Replace("IF((#>=" & st & ")*(#<=" & en & ")," & va & ","""")", "#", tm))
End With
End Sub 

[ISMI]

Hello My friend,

I have then 2 other question, is it possible to solve 2 issues with your quick method:
1) in column LN i need to put time values from 0 to defined parameter (in example file = 57311 rows). With simple increment it tooks a lot of time.
2) i need to find SUM for each row (from 6 to 57311, from column A to LM) and put resulting value in column LP. With simple increment it tooks a lot of time.

Starting column always A, but end column can vary. Would be nice to have some king of automatic search.

Thx a lot for support!

[ISMI]

Sintek, your method works, but table 57000 rows * 325 columns it tooks about 4-5 min
Bo_Ry: you method works also and the fastest one, for same table it tooks approx 20-30 sec.

Thank you to both of you, you guys are crazy and i don't know how you do that, amazing job. Greetings and all the best

[Sintek]

4-5 min...that is forever in coding terms lol...

[ISMI]

yea, for small table it is very fast, but then table is huge a lot of calcs in background. My laptop is intel i7 with 16 Gb RAM, not Pentium 1 so should ok for this kind of calcs

[Sintek]

Quick Question...Has Col U got numbers from 0 to X in increments of 1 in order...

[ISMI]

Quick Question...Has Col U got numbers from 0 to X in increments of 1 in order...

Hello my friend,
Yes in column U (time) i have minutes 0, 1, 2, ..., N (time horizont is always defined parameter)

[Sintek]

i need to find SUM for each row (from 6 to 57311, from column A to LM) and put resulting value in column LP.

Sub J3v16()
Dim Mx As Long, i As Long, lc As Long, St As Long, Fn As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Mx = Application.Max(Range(Cells(1, 1), Cells(2, lc)))
'Rows(6 & ":" & Mx).ClearContents
With Cells(6, lc + 1).Resize(Mx + 1)
    .Value = Evaluate("=Row(" & .Address & ")-6")
End With
For i = 1 To lc
    St = Cells(1, i).Value: Fn = Cells(2, i).Value
    Cells(St + 6, i).Resize(Fn - St + 1).Value = Cells(3, i)
Next i
Cells(6, lc + 3).Resize(Mx + 1).FormulaR1C1 = "=SUM(RC[-" & lc + 2 & "]:RC[-3])"
End Sub

[ISMI]

it works, 1 sec, my friend shake your hand again!!!!

[Sintek]

it works, 1 sec

Now that's better than 4 mins lol