CODEWITHSUNDEEP
excelvba
Michael
Michael

Reputation: 3

Excel VBA Code to check a value each cell in column then recalculate

I have a workbook with a set of Prices, I need to randomly assign margins to them but they all have to be lower then .88 % of List ...

Trying to write code that will go through and check every cell in a column if any of the values are > .88 then Excel will do a 'Calculate' and it will check again to see if any cells are > .88 until all cells in column are below .88

I've tried this code it goes through each cell and does a calculate but isn't checking to see if cells are > .88

` 

 Sheets("Line Breakout Revised").Select


 For i = 1 To Range("U" & Rows.Count).End(xlUp).Row

    If Cells(i, 1).Value > 0.88 Then Calculate

Next i





End Sub

Upvotes: 0

Views: 1152

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You can use a do loop using MAX():

Dim i As Long
i = 0
Dim Threshold As Long
Threshold = 1000
With Worksheets("Line Breakout Revised")
    Do
        .Calculate
        i = i + 1
    Loop Until Application.WorksheetFunction.Max(.Range(.Cells(1, "U"), .Cells(.Rows.Count, "U").End(xlUp))) <= 0.88 _
        Or i >= Threshold
End With
If i >= Threshold Then MsgBox "Threshold met before accomplishing goal"

I put a counter in to make sure a indefinite loop does not occur. It will loop till all the numbers are lower or equal to .88 OR the loop has gone 1000 times. You can adjust the threshold

Upvotes: 0

Related Questions