Looping through a range

[Shawn]

Hi,

I'm pretty new at learning Excel vba and I'm trying to loop through some
cell values to find the largest total. For example, if cells A1:C3 contain
the following values:

1,2,3
4,6,8
5,9,7

I'm trying to find the largest total of two cells but excluding cells in
the same row (ie A1+A2, A1+A3, A1+B2, A1+B3, B1+A2, B1+A3 etc). So far
I've got the following loops:

Range("A1").Select
For i = 0 To 2
    For j = 1 To 2
            tempValue = ActiveCell.Value
            tempValue2 = ActiveCell.Offset(j, i).Value
            tempTotal = tempValue + tempValue2
            If tempTotal > total Then total = tempTotal
    Next j
Next i

These seem to do what I need for working down and across from cell A1 but
I'm not sure how to extend this to include combinations like B1+A2, B1+B2 &
B1+C2. Am I missing something really obvious as to how to go about this or
can someone point me in the right direction?

Thanks

Shawn

 

[Tom Ogilvy]

should get your max value.

Sub ABC()
rw = 10
total = 0
Range("A1").Select
For i = 0 To 2
For j = 0 To 2
tempValue = ActiveCell.Offset(i, j)
For k = i + 1 To 2
For L = 0 To 2
tempValue2 = ActiveCell.Offset(k, L)
tempTotal = tempValue + tempValue2
If k <> i Then
Cells(rw, 1) = ActiveCell.Offset(i, j)
Cells(rw, 2) = ActiveCell.Offset(k, L)
Cells(rw, 3) = tempTotal
rw = rw + 1
If tempTotal > total Then total = tempTotal
End If
Next L
Next k
Next j
Next i

End Sub

 

[Shawn]

should get your max value.

Sub ABC()
rw = 10
total = 0
Range("A1").Select
For i = 0 To 2
For j = 0 To 2
tempValue = ActiveCell.Offset(i, j)
For k = i + 1 To 2
For L = 0 To 2
tempValue2 = ActiveCell.Offset(k, L)
tempTotal = tempValue + tempValue2
If k <> i Then
Cells(rw, 1) = ActiveCell.Offset(i, j)
Cells(rw, 2) = ActiveCell.Offset(k, L)
Cells(rw, 3) = tempTotal
rw = rw + 1
If tempTotal > total Then total = tempTotal
End If
Next L
Next k
Next j
Next i

End Sub

Hi Tom,

Thank you for such a quick reply. I'll give the above code a try. It
didn't occur to me to use "if/then" statements like that.

Thanks again.

Shawn