Looping through a range:find the largest total.

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

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


--
Regards,
Tom Ogilvy




"Shawn" <[email protected]> wrote in message
news:[email protected]...
> 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

On Mon, 03 Oct 2005 15:38:26 -0400, Tom Ogilvy wrote:

> 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