here is a little example of how to use excel for what you need ... this subrutine find how many times a value, that is between a range, appears ... i hope you find it useful ...
CODE
Private Sub LoadExcelFile()
Dim FileDir As String
Dim Row, Col As Integer
Dim xlsApp As Excel.Application
Dim Book As Excel.Workbook
Dim Sheet1 As Excel.Worksheet
Dim Sheet2 As Excel.Worksheet
On Error GoTo xError
' Assign object references to the variables. Use
' Add methods to create new workbook and worksheet
' objects.
FileDir = App.Path & "\MyFile.xls"
Set xlApp = New Excel.Application
xlApp.Visible = True
Set Book = xlApp.Workbooks.Open(FileName:=FileDir, ReadOnly:=False)
Book.Activate
Set Sheet1 = Book.Worksheets("Sheet1")
Sheet1.Activate
Set Sheet2 = Book.Worksheets("Sheet2")
For Row = 1 To 10
For Col = 1 To 5
' here we view is the value of the cell
' is between the range we need
If (Val(Sheet1.Cells(Row, Col)) > 1) And _
(Val(Sheet1.Cells(Row, Col)) < 10) Then
Sheet2.Activate
Sheet2.Cells(Sheet1.Cells(Row, Col), 1) = Sheet1.Cells(Row, Col)
Sheet2.Cells(Sheet1.Cells(Row, Col), 2) = _
Val(Sheet2.Cells(Sheet1.Cells(Row, Col), 2)) + 1
Sheet1.Activate
End If
Next Col
Next Row
' we save the changes
Book.Save
' close the objects
Book.Close
xlApp.Quit
' clean them from memory
Set xlApp = Nothing
Set Book = Nothing
Set Sheet = Nothing
Exit Sub
xError:
MsgBox "An error ocurred during the process"
End Sub
here is the matrix is use for the calc
the results appear in the second sheet
8 15 7 13 58
4 12 5 14 1
12 4 3 51 63
5 5 3 12 8
2 2 6 18 3
5 5 3 95 43
11 1 5 5 3
3 4 5 2 1
1 2 6 23 7
8 54 83 62 121
and the results i obtained are these
Comment/Reply (w/o sign-up)
