Looping through a range:find the largest total.

1. 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 &
can someone point me in the right direction?

Thanks

Shawn  Register To Reply

2. re: Looping through a range:find the largest total.

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

> 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

&
> can someone point me in the right direction?
>
> Thanks
>
> Shawn  Register To Reply

3. re: Looping through a range:find the largest total.

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  Register To Reply