Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Create list of Find Values (Excel 2000>)

    Hi,
    I had to generate a list of cars on a new sheet based on finding them on multiple sheets. I found a great bit of sample code in the help files of VBA which i was able to use with great effect in my macro. The loop structure:
    <pre> Set CT = .Find(CarType, After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False)
    If Not CT Is Nothing Then
    FirstAddress = CT.Address
    Do
    i = i + 1
    CT.EntireRow.Copy Sheets(1).Range("A" & i)
    Set CT = .FindNext(CT)
    Loop While Not CT Is Nothing And CT.Address <> FirstAddress
    </pre>

    assigns a cell reference to the found value, and this you can use to paste, or (anything else) you need to do with a range! I guess this could also help with a recent <post#=484580>post 484580</post#> I read?

    The macro in the sample workbook works well, but if any VBA guru's have the time...can you please advise me if the code is good, or if any further structural/useful improvements can be made! I am ALWAYS willing to learn more and be advised on improvements!

    Cheers
    Regards,
    Rudi

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

    Re: Create list of Find Values (Excel 2000>)

    Most of the code is OK, but the line

    .Value = "List of " & CarType & "'s from " & Sh & " Country Sheets!"

    will report the wrong number of sheets:
    - At the end of the For .. Next loop, Sh will be the number of sheets + 1
    - The newly inserted sheet shouldn't be counted, you actually want the number of sheets - 1.

    So change the line to

    .Value = "List of " & CarType & "'s from " & (Sh - 2) & " Country Sheets!"

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> <small>The country to the south of where I live is called <big>Belgium</big> in English, not <big>Belguim</big>.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create list of Find Values (Excel 2000>)

    Thanx for that. I made the adjustment on the Belgium sheet! However, the sheet count will not work by saying (Sh - 2). I plan to run this procedure for say; 3 or 5 different cars at any point, and then the (-2) must increment each time within the WBook! This is an interesting problem you picked up! (Strange that I didn't even note that!) What might be done here is to count the sheets starting at Australia, or create a static value (although i don't think that will work!) Any ideas to get around this? I cant say Sheets.count(Australia:Sheets(xlEnd).count)????
    Regards,
    Rudi

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

    Re: Create list of Find Values (Excel 2000>)

    If you want to use it like that, your code will have to be adjusted in other ways to, since the loop shouldn't start at 2 any more.

    It would be easier to put all data into one sheet, since you have a column to indicate country anyway. That way, you don't have to loop through sheets; you can refer to the single fixed data sheet.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create list of Find Values (Excel 2000>)

    Its true what you suggest! I initialy thought it would be good to have the results on a seperate sheet, but in retrospect, it seems to be good to have the results on one sheet anyways!

    I will play around with the idea! Tx

    PS: Just thinking...if I do run it one one sheet, I may need to recode that it must paste the next car type below the previous result. So I will need to locate the next blank line below the data. I will use Range("A65536").xlUp.Select.
    Regards,
    Rudi

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

    Re: Create list of Find Values (Excel 2000>)

    If you place the results in one sheet, you should create it in advance, not in the code. Or test in the code whether the result sheet already exists, and only create it if it doesn't exist yet.

    In my previous reply, I suggested placing all data in one sheet (i.e. the data for Australia, Belgium, Denmark)

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create list of Find Values (Excel 2000>)

    Yes. I understood your previous post! I will create the code that adds the car types to one sheet, and post it back later today for you to review! Thanx
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create list of Find Values (Excel 2000>)

    Hans, here is a version that lists the cars on only ONE sheet! Could you review the code and see if it is OK. Any comments or improvements! Thanx
    Regards,
    Rudi

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

    Re: Create list of Find Values (Excel 2000>)

    Your code doesn't work correctly if the Car List sheet already exists, but is not the active sheet. I have attached a version that hopefully works better in that situation.

    I rearranged the code a bit and corrected some typos (the multiple of car is cars, not car's, and the past tense of occur is occurred, not occured.)

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create list of Find Values (Excel 2000>)

    Thanx. You can rap me over the knuckles for those tense based errors.

    Thanx for the corrections and the tightening of the edges. I like the way you exit the sub if the inputbox is cancelled or left blank too. Nice~!
    Regards,
    Rudi

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create list of Find Values (Excel 2000>)

    My Flexfind has a "List" button to list all found entries. Is that what you're after?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create list of Find Values (Excel 2000>)

    Yeah, I recall you mentioning this is a previous post. Thanx Jan, i should look into it too...and save time having to re-invent the wheel!
    Cheers
    Regards,
    Rudi

Posting Permissions

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