EXCEL: selecting rows across several worksheets programatically 1

[ADoozer]

im having a bit of trouble moving from VB into VBA.

the xls file has 11 sheets
FrontPage, Sport1, Sport2,... Sport10
Sport1 to Sport10 contain 6 columns of Data with anything from 10 to 1000 rows

what i need to do is:-
call a subroutine from a button on "FrontPage".
an input box apears and the user enters a search criteria.
the search criteria is checked against all cells in column C.
if the criteria matches i need to copy the full row to a new sheet

now this is fine, if i just want to get rows from a single worksheet, but i need to search all 10 "Sport" worksheets

here is my bodged code for creating a search from 1 worksheet

Dim SelectString As String

    SelectString = ""
    
    'ask the user to select a part for example 2.1
    SearchFor = InputBox("Enter a part", "", "0")
    
    'if no part was entered or cancel was pressed, exit sub
    If SearchFor = "" Or SearchFor = "0" Then
        Exit Sub
    End If
    
    'lets look though all the cells in colum C for our part
    For i = 2 To 32000
        If Cells(i, 3) = CDbl(SearchFor) Then
            'add the row to our selection string
            SelectString = SelectString & "A" & i & ":" & "F" & i & ","
        ElseIf IsEmpty(Cells(i, 3)) Then
            'if the cell is empty assume were at the bottom
            'of the list and exit loop
            Exit For
        End If
    Next i
    
    'remome trailing ","
    SelectString = Mid(SelectString, 1, Len(SelectString) - 1)
    
    'select our selection string
    Range(SelectString).Select
    'copy selection
    Selection.Copy
    'add a new worksheet
    Worksheets.Add
    'rename the sheet
    ActiveSheet.Name = "SearchResults_" & SearchFor
    'paste the info
    ActiveSheet.Paste

ive experimented using

Selection.Clear
    'loop through all worksheets
    For Each Worksheet In Worksheets
        SelectString = ""
        If Not Worksheet.Name = "FrontPage" Then
            For i = 2 To 32000
                If Worksheet.Cells(i, 3) = dblSearchFor Then
                    SelectString = SelectString & "A" & i & ":" & "F" & i & ","
                ElseIf IsEmpty(Worksheet.Cells(i, 3)) Then
                    SelectString = Mid(SelectString, 1, Len(SelectString) - 1)
                    Worksheet.Range(SelectString).Select
                    Exit For
                End If
            Next i
        End If
    Next Worksheet

in an attempt to select across all 10 worksheets, but this doesnt seem to want to work.


any pointers how to neaten the code up, and obviously get it working, greatly appreciated


If somethings hard to do, its not worth doing - Homer Simpson

 

[SkipVought]

Hi,

1. Where is the assignment for dblSearchFor?

2. Worksheet is a RESERVE word and should not be used as an object variable.

3. What are you doing with the SelectString range on each worksheet after it's selected and copied into a new sheet?

I'd suggest using MS Query on a new sheet to query the Sportn sheets with the appropriate criteria. It would be EXTERMELY simple -- far less code than you are using; maybe none at all!


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[ADoozer]

1)

Private Sub FindAllInstances(dblSearchFor As Double)

i didnt paste the full code, just the loop. however it passed into my sub

2)

For Each Worksheet In Worksheets
Next Worksheet

thats how i usualy loop through the worksheets collection in VB, but im open for suggestions

3)do you mean in the second snippet of code?
what im trying to do is select multiple ranges across the workbook

pseudocode
Worksheet("Sport1").Range("A1:F1",A4:F4","A8:F8").select
Worksheet("Sport2").Range("A1:F1",A3:F3","A15:F15").select
...
Worksheet("Sport10").Range("A5:F5",A7:F7","A8:F8").select
end pseudocode

then the plan was to copy the selection and paste into my new sheet

MS Query? not sure what that is?

If somethings hard to do, its not worth doing - Homer Simpson

 

[SkipVought]

1. you posted the DECLARATION and not the ASSIGNMENT.

2. go ahead and use reserve works. Be aware of the Law of Unintended Consequences.

3. I did not ask for CODE. I asked, "What are you doing ..." I expected a narrative of intention.

4. MS Query: Menu Item Data/Get External Data/new Database Query -- Excel Files - YOUR EXCEL WORKBOOK -- Sport1.....

Once a Querytable is added to your sheet, it's there until you delete it. Refresh via Data/Refresh. After it's added, turn on your macro recorder and EDIT THE QUERY. View the code and it will open VISTAS of possibilities!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

Hay Doozer - what version of Excel are you using ??

If you are on XP or higher, there is a property of the FIND method that allows you to search the whole workbook

In fact, you should probably be using the FIND method anyway as it is far quicker than looping 32000 times !

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[ADoozer]

skip:
1: i fail to see where your going on this. my search for "ranges" is working. the problem is selecting across workbooks.

however i use cdbl on a string returned from inputbox, do a few checks to make sure it is valid then pass it into the sub.

2: ok got you now.. it was a typo on my behalf. should read
For Each WrkSht in worksheets...
i thought u were implying i couldnt itterate through the worksheets collection.

3: still not sure what your asking, SelectString is just that, its a string containing the cells i wish to select.

4: tried to use query, but it informs me there are no tables set up.

[NOTE]
i am writing this code pretty much blind as a help to a friend of mine.
i do not have a copy of the actual xls file, just a dummy i have mocked up.
i was asked, "can u select multiple rows from multiple worksheets based on a selection criteria and copy to a new worksheet." this is what im attempting to do.

geoff: i assume it is office 2k but im not 100% sure, was also hoping there would be an easier way to search the colums and that looks like it (p.s. 32000 was just a number i picked, it should never do a full 32000 cycles)

thnx

If somethings hard to do, its not worth doing - Homer Simpson

 

[SkipVought]

WORKBOOKS?

You NEVER mentioned multiple WORKBOOKS!

You DID state...

"what im trying to do is select multiple ranges across the workbook" _{my emphasis}

With regard to SelectString, a Range Reference INCLUDES the SHEET along with the cell range.



Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[xlbo]

Doozer - certainly use the FIND function - if you don't have the property to search across the entire workbook you could use a construct like:

For each wks in thisworkbook.worksheets
 set fCell = wks.columns("A").find(what:="TheSearchString", lookin:=xlvalues, lookat:=xlpart)
    if not fCell is nothing then
       'match found
    else
       'no match found
    end if
next

If it can appear more than once in each sheet, you will need to use the FINDNEXT method - there is a very good example in the help files - if you can't find it let me know and I'll post back


Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[ADoozer]

WORKSHEETS... ive had a hard day.

anyway needs a bit of error checking but the following code does the job.

Dim newWks As Worksheet
Dim wks As Worksheet
Dim myCell As Range
Dim SelectString As String
Dim myCount As Integer
Dim myLast As Integer

Public Sub FindAllInstances(dblSearchFor As Double)

    For Each wks In ThisWorkbook.Worksheets
        If Mid(wks.Name, 1, 14) = "SearchResults" Then
            wks.Delete
            Exit For
        End If
    Next wks
    
    Set newWks = Worksheets.Add(after:=Worksheets(1))
    newWks.Name = "SearchResults"
    
    SelectString = ""
    myCount = 1
    myLast = 1
    For Each wks In ThisWorkbook.Worksheets
        Set myCell = wks.Columns("C").Find(dblSearchFor)
        If Not myCell Is Nothing Then
            wks.Activate
            firstaddress = myCell.Address
            Do
                SelectString = SelectString & "A" & myCell.Row & ":" & "F" & myCell.Row & ","
                myCount = myCount + 1
                Set myCell = wks.Columns("C").FindNext(myCell)
            Loop While Not myCell Is Nothing And myCell.Address <> firstaddress
            SelectString = Mid(SelectString, 1, Len(SelectString) - 1)
            wks.Range(SelectString).Copy
            newWks.Range("A" & myLast).PasteSpecial (xlPasteValues)
            myLast = myCount
            SelectString = ""
        End If
    Next wks
    newWks.Activate
    newWks.Range("A1").Select
End Sub

thnx both

If somethings hard to do, its not worth doing - Homer Simpson

 

[xlbo]

glad you got it working Doozer

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question