Looping through a range
 

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

Looping through a range
 

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" wrote in message
news:pan.2005.10.03.19.08.36.19302@noemailaddress. com...
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

Looping through a range
 

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