Ms Excel : Search A Sheet

[kvarnerexpress 0]

Anybody out there good with VBA and Excel? I am trying to search a sheet in a workbook (Sheet1), find out how many times particular values exists (int 1 to 54), then create a new workbook with a list of the values checked for and how many times they appeared.Been googling this, but everything I have found just confused me more, never done much with Office before. Thanks in advance for any help!!By the way, I am using Excel 2003 if that matters...

[lava.alt 0]

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 ...

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.WorksheetOn 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 = NothingExit SubxError: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

[lava.alt 0]

sorry i forgot to put the results2 83 124 65 166 47 48 6i recomend you to debug the program so you undestand what is doing ...

[ARNEL 0]

Anybody out there good with VBA and Excel? I am trying to search a sheet in a workbook (Sheet1), find out how many times particular values exists (int 1 to 54), then create a new workbook with a list of the values checked for and how many times they appeared.

Been googling this, but everything I have found just confused me more, never done much with Office before. Thanks in advance for any help!!

 

By the way, I am using Excel 2003 if that matters...

148509[/snapback]

Lava.alt posted a nice script for your que, fore more info, try to look for windows help files from microsoft excel, there you will find a lot of tips with regards to your topics....

[dul 0]

At the moment macro language is better /MS offices/. You can use any MS Office macro command via OLE /Object Embedding Language/. It is quickest and easiest way.