finding max value

[zain zahran]

In Excel we can do this by =max(range) but I cannot figure out how to do it in macro.
For example, I have the data in two columns:
1 2
1 3
2 1
2 4
3 1
4 2

The table means that ID#1 is related to ID#2 and 3, etc.
I want to make a macro to find the max of column 1 (which is 4). Then make a nxn matrix, n is the max of col#1 = 4.
The matrix will be
1 1 1 0
1 1 0 1
1 0 1 0
0 1 0 1

1 means related, 0 means not related.

Can anyone help? Thanks!

[ This Message was edited by: zain zahran on 2002-04-07 09:53 ]

[ This Message was edited by: zain zahran on 2002-04-07 09:55 ]

[Tom Schreiner]

Sub FindMax()
Dim YourRange As Range
Dim MaxNum
'define the range
Set YourRange = Worksheets("Sheet1").Range("A1:A10")
MaxNum = Application.WorksheetFunction.Max(YourRange)
'your answer is...
MsgBox MaxNum
End Sub

Search VBA help for:
"Using Microsoft Excel Worksheet Functions in Visual Basic"
Have a nice day!
Tom

[smozgur]

Paste this code in Sheet1 module:

Private Sub CreateMatrix(rng As Range)
Dim tmpVal
Dim i As Integer, j As Integer
Dim c As Integer, r As Integer

For i = 1 To rng.Rows.Count
If rng(i, 1).Value > tmpVal Then
tmpVal = rng(i, 1).Value
End If
Next i
Cells(rng.Row, rng.Column + 3).Select
r = ActiveCell.Row - 1
c = ActiveCell.Column - 1
For i = 1 To tmpVal
For j = 1 To tmpVal
Cells(r + i, c + j).Value = 0
If i = j Then
Cells(r + i, c + j).Value = 1
End If
Next j
Next i
For i = 1 To rng.Rows.Count
Cells(r + rng(i, 1).Value, c + rng(i, 2).Value).Value = 1
Next i
End Sub


Locate your numbers in A1:B6 as you gave sample above. And run this line of code in immediate window:

Call CreateMatrix(range("A1:B6"))

You can modify it however you need.

regards

_________________
Oz ~ TheWordExpert

[ This Message was edited by: smozgur on 2002-04-07 15:12 ]

[zain zahran]

Thanks to TsTom and Smozgur!!!!