CODEWITHSUNDEEP
vbaexcel
OykuA
OykuA

Reputation: 81

Finding max value of a loop with VBA

I am trying to find max value of a loop. First, I have two random arrays and I want to find these two arrays correlation coefficient. Then, I want to calculate it multiple times as much as "I3" cell. After that, I want to write a code which finds max correlation coefficient from this calculation. I wrote the code below but it didn't work. 

Sub Macro1()

Dim i As Long
For i = 1 To Range("I3")
    Calculate
Next

DMax = Application.WorksheetFunction.Max("A2")
Range("I4").Value = DMax

End Sub

Any help is appreciated.

Upvotes: 0

Views: 7309

Answers (3)

Hank
Hank

Reputation: 187

For i = 1 To Range("I3").value 'to calculate from 1 to the value in that cell

what i would recommend for your question.

For i = 1 To 10 ' loop 10 times
    For j = 1 To i ' here it will allow you to check your stuff multiple times before moving on to the next value
    arr1(i) = arr2(j) ' see if your array match
    Next j

Next i

Upvotes: 0

Andi
Andi

Reputation: 230

Your Max-Function needs a proper argument. Just typing "A2" doesn't work in VBA. Try:

DMax = Application.WorksheetFunction.Max(Range("A2"))

This will give you the max-Value of the Array A2. But keep in mind that the max-Value of a range consisting of a single cell is always the cell value.

If you want to calculate the maximum value of all iterations, you should use the max-function in each iteration (inside for-loop) and store it's value. In each following iteration you should then overwrite the max-Value if your new max value is larger than the old one. Just like this:

Sub Macro1()

Dim i As Long
DMax = 0
For i = 1 To Range("I3")
    Calculate
    DMax2 = Application.WorksheetFunction.Max(Range(...))
    If DMax2 > DMax Then DMax = DMax2
Next i

Range("I4").Value = DMax

This will give you the max-Value of Range(...) of all iterations.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

I barely understand your code, but the solution will be nasted loop. Suppose you have two sets of numbers: A2 (Cells(2, 1)) through I2 (Cells(2, 7)) and A3 (Cells(3, 1)) through I3 (Cells(3, 7)). You want to calculate partial correlation and find what was the maximum value of it.

For i = 1 To 7
    For j = 1 To i
        'Calculate the correlation
    Next j
    'here you have partial coefficient and you can compare it,
    'if it is greater than previous one then save it and store
Next i

Upvotes: 0

Related Questions