comparing 9 columns

[fredrerik84]

Hi my head is spinning here a little im trying to compere 9 column number to find highest values (also this number will be expanded later)

I want to return the highest number of these 3 Columns"J" "N" "R" result stored as variable double
I want to return the highest number of these 3 Columns"K" "O" "S" result stored as variable2 double
I want to return the highest number of these 3 Columns"L" "P" "T" result stored as variable3 double

results will be var1 + var2 + var3 in column W

Is this possible to to somehow? some of the rows in the columns might also be empty
data rows start from 12 (header in row 11) its around 200-300 data rows (varies)

I started making a for loop code for this but all my criterias makes me dizzy :/ hope someone a little more skilled know a good solution for this

 

[Fluff]

Is this what your looking for
=MAX(J12,N12,R12)&"+"&MAX(K12,O12,S12)&"+"&MAX(L12,P12,T12)

 

[fredrerik84]

Hi thanks for your reply.
i was not able to test this in cell for some reason., so maybe yes its what im looking for but it must be a vba code or else it will not work with the rest of my setup.

 

[Marcelo Branco]

Maybe something like this

Sub aTest()
    Dim arrCols As Variant, arrVar() As Double, i As Long
    Dim dbResult As Double
    
    arrCols = Array("J12:J400,N12:N400,R12:R400", "K12:K400,O12:O400,S12:S400", "L12:L400,P12:P400,T12:T400")
    ReDim arrVar(0 To UBound(arrCols))
    For i = LBound(arrCols) To UBound(arrCols)
        arrVar(i) = Application.Max(Range(arrCols(i)))
        dbResult = dbResult + arrVar(i)
    Next i
    MsgBox dbResult
End Sub

Remark: 400 is an arbitrary number. You can improve the code getting the lastRow with data.

M.

 

[fredrerik84]

Maybe something like this

Sub aTest()
    Dim arrCols As Variant, arrVar() As Double, i As Long
    Dim dbResult As Double
    
    arrCols = Array("J12:J400,N12:N400,R12:R400", "K12:K400,O12:O400,S12:S400", "L12:L400,P12:P400,T12:T400")
    ReDim arrVar(0 To UBound(arrCols))
    For i = LBound(arrCols) To UBound(arrCols)
        arrVar(i) = Application.Max(Range(arrCols(i)))
        dbResult = dbResult + arrVar(i)
    Next i
    MsgBox dbResult
End Sub

Remark: 400 is an arbitrary number. You can improve the code getting the lastRow with data.

M.

this looks like the code i would be interested in, but its not working like I expected or maybe im doing something wrong. and also this code is atm to advanced for me to fully understand.

what I want is is max number of column J,N,R to be displayed in column X and stored as a variable that I can mess around with for further calculations, and

max number of column K,O,S to be displayed in column Y ( and stored as a variable)

max number of column L,P,T to be displayed in column Z ( and stored as a variable)

(or you could include the final calculation , which would be var1= 1 / var1 * 100
var2 = 1 / var2 * 100
var3 = 1 / var * 100
Cells(i, "r").value = value & "%"

 

[fredrerik84]

I hope this didn't become to confusing ? ill try one more time

Highest number of column J,N,R to column X
Highest number of column K,O,S to column Y
Highest number of column L,P,T to column Z

Column AA will be final result which will be
x= 1 / x * 100
y = 1 / y * 100
z = 1 / z * 100

AA = value = 100 - (x+ y + z) & %

SOrry for confusing post

 

[Marcelo Branco]

In my code above the max of the columns are contained in arrVar.
arrVar(0) --> max of J, N, R
arrVar(1) --> max of K, O, S
arrVar(2) --> max of L, P, T

M.

 

[fredrerik84]

In my code above the max of the columns are contained in arrVar.
arrVar(0) --> max of J, N, R
arrVar(1) --> max of K, O, S
arrVar(2) --> max of L, P, T

M.

ok its a real neat code you have made here, I need to learn how to code this. will this be correct usage ?

    Dim arrCols As Variant, arrVar() As Double, i As Long
    Dim dbResult As Double
    
    arrCols = Array("J12:J400,N12:N400,R12:R400", "K12:K400,O12:O400,S12:S400", "L12:L400,P12:P400,T12:T400")
    ReDim arrVar(0 To UBound(arrCols))
    For i = LBound(arrCols) To UBound(arrCols)
        arrVar(i) = Application.Max(Range(arrCols(i)))
        dbResult = dbResult + arrVar(i)
        cells(i, "X").value = arrVar(0)
        cells(i, "Y").value = arrVar(1)
        cells(i, Z").value = arrVar(2)
        cells(i, AA").value = dbResult 
    Next i
    MsgBox dbResult

whats stored in dbResult ?

 

[Marcelo Branco]

No, no, this doesn't work - the var i goes from 0 to 2 in the loop, so cells(0,"X") doesn't seems correct

If you do want to pass the values inside the loop i would try something like (not tested)

For i = LBound(arrCols) To UBound(arrCols)
    ArrVar(i) = Application.Max(Range(arrCols(i)))
    dbResult = dbResult + ArrVar(i)
    Select Case i
        Case 0
            Cells(2, "X") = ArrVar(i)
        Case 1
            Cells(2, "Y") = ArrVar(i)
        Case 2
            Cells(2, "Z") = ArrVar(i)
    End Select
Next i
Cells(2, "AA").Value = dbResult

M.

 

[fredrerik84]

I tried as you suggested, but I think I messed up the code a little:/
if I understand this correct your code loop trough the arrcol which is set in the beginning of the code? .
would it be possible to modify your code a little so I can work with it? so it just displays the max values for each row range into column XYZ as I asked about in first post

here is what I have tried:

Sub aTest()
    Dim arrCols As Variant, arrVar() As Double, i As Long
    
    arrCols = Array("J12:J400,N12:N400,R12:R400", "K12:K400,O12:O400,S12:S400", "L12:L400,P12:P400,T12:T400")
    ReDim arrVar(0 To UBound(arrCols))
    For i = LBound(arrCols) To UBound(arrCols)
    arrVar(i) = Application.Max(Range(arrCols(i)))
    Select Case i
        Case 0
            Cells(2, "X") = arrVar(i)
        Case 1
            Cells(2, "Y") = arrVar(i)
        Case 2
            Cells(2, "Z") = arrVar(i)
    End Select
Next i
End Sub

and im not if you understand the criteria? its should be based on rows its like prices from different stores

cola price: 2,02 2,01 2,3
pepsi: 2,,02 2.2 2.2
some item: 2,0 1 2.2 2.2
some item: 2,0 1 2.2 2.2
some item: 2,0 1 2.2 2.2
some item: 2,0 1 2.2 2.2
200-1000 rows or something

Best price will be stored in column XYZ