CODEWITHSUNDEEP
excelloopsvba
Matt Le Tesier
Matt Le Tesier

Reputation: 11

Excel, loop through multiple calculations

I have written a simple code to display the highest value from a changing cell value which is connected to an external data source. I have 400 different connections and would like to repeat this calculation for each one, my code just updates the first row:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim HighVal As Integer
Dim LivePrice As Integer

LivePrice = Range("E7")
HighVal = Range("G7")

If LivePrice > HighVal Then Range("G7") = LivePrice
End Sub

Quite stumped at the most efficient way to do this.

Upvotes: 0

Views: 97

Answers (1)

David Glickman
David Glickman

Reputation: 805

I don't know exactly how your file is laid out, and am not sure why you are replacing cell B7 with the contents of B7. The way to loop is as follows.

Assuming that all your connections are in separate rows, B7, C7 etc you would write a loop like this.

    dim i as long

    for i = 2 To 400
        LivePrice = Cells(i, 7).Value HighVal = Cells(i, 7).Value
        If LivePrice > HighVal Then Cells(i, 7).Value = LivePrice
    next i

This will loop through from 2 to 400, which will be all the rows from B till the end. For each row it will perform your calculation on Cell(Row Number, 7).

Upvotes: 1

Related Questions