Results 1 to 14 of 14
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find in multiple sheet... (2000 sr 1)

    Assuming i have this USER = Sheets("RATE").Cells(RIGA, "AI").Value

    and i want to serach the value USER in SHEETa1,SHEETb2,SHEETv3...SHEETc5, and all sheet have the same range for search G2:G5000, now if this value USER is present in one range G2:G50000 of sheets insert Me.TextBox13.Value = the name of the sheet that contain the value USER...
    Possible...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    Do you want to search all sheets in the workbook, or all sheets except one or two, or just a specific list of sheets? Please provide sufficient infornation.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    SURE YOU HAVE UNDESTAND ME!!!!!!!!!!!!
    After 3 years....

    Yes! not for all but ignore sheet GAF and SERVIZIO

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    Try this:

    Dim wsh As Worksheet
    For Each wsh In Worksheets
    Select Case wsh.Name
    Case "GAF", "SERVIZIO"
    ' Skip these sheets
    Case Else
    If Not wsh.Range("G2:G5000").Find(What:=User, _
    LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
    Me.TextBox13 = wsh.Name
    Exit For
    End If
    End Select
    Next wsh

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    work perfect but with you now i "love" the metod function...
    Is possible to similar:

    cercavalore Me.TextBox10.Value

    where cercavalore is the name of function

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    Function CercaValore(Utente As String) As String
    Dim wsh As Worksheet
    For Each wsh In Worksheets
    Select Case wsh.Name
    Case "GAF", "SERVIZIO"
    ' Skip these sheets
    Case Else
    If Not wsh.Range("G2:G5000").Find(What:=Utente, _
    LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
    CercaValore = wsh.Name
    Exit For
    End If
    End Select
    Next wsh
    End Function

    Use like this:

    Me.TextBox13 = CercaValore(Me.TextBox10)

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    KISS!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    "ON FLY"

    Asuming i have this Sheets("GAF").Range("N2:O9000").ClearContents and

    i want to use this code to clear all value in column and i have:

    FOGLIO = "CORPORATE"
    COLONNA = "S"
    Sheets(FOGLIO).Range("COLONNA" & 2:"COLONNA" & 5000").ClearContents

    Have error, please correct me.
    in effect i want to clear all in column S in sheet CORPORATE
    Tks.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    The " and & are placed incorrectly:
    <code>
    Range(COLONNA & "2:" & COLONNA & "5000")</code>

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    Is good way to arrange this function to solve this:
    I would want with a macro use the sheet STATISTICA similar a pivot (but repeat i dont want a pivot).
    Bsed on column H and column D of GAF, scanning in all cells of columns H and if is found a value present in one sheet CORPORTAE, RETAIL_POE, PUBB_AMM, LARGE_COR, SCARTI, (have the same range for all sheet G2:G500) add 1 in cell refred column D of GAF and column A of STATISTICA fill the cell in STATISTICA based column D...

    Example:
    cell H2 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C25 of STATISTICA because in A25 is present 4549...

    cell H3 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C31 of STATISTICA because in A31 is present 4561...

    cell H4 in GAF contain "" put 1 in G25 of STATISTICA because in A25 is present 4549...

    cell H5 in GAF contain 78 this value is present only in sheet RETAIL_POE in range G2:G500 put 1 in C30 of STATISTICA because in A30 is present 4558...
    ........

    cell H51 in GAF contain 11 this value is present only in sheet CORPORATE in range G2:G500 put 1 in B2 of STATISTICA because in A2 is present 4500...

    naturally if the value in H of GAF is found for many matching, ad +1 the the old value stored cells of STATISTICA

    Hope i am clear...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    Sal,

    You are asking far too much. You should know by now that we are not going to create complete solutions for you.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    sure, but i want only if the function in this post is a godd way to init the project i dont want the solution macro...;-)
    Peraphs is good ise to use sttement INTERSEC...., boh?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find in multiple sheet... (2000 sr 1)

    I suggest that you take a look at all the code you have received from the Lounge and other sites; there are several examples that show how to search through worksheets.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •