Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Find for Data (VBA/Excel/2002-3)

    I have a spreadsheet that is dumped from a financial package.

    It lists name, address, and phone details that I want to extract from Sheet1 and paste into a layout (that is easier to read) on a new sheet called Client Listings.

    I have developed code that does the following:
    <UL><LI>Creates a new sheet Client Listings
    <LI>Creates headings in cells A3, B3, C3 and D3 - Name , Address , Phone Number , Mobile Number
    <LI>Moves to Sheet1 finds the first instance of Name (changes it to FoundName so that it won't search there again), copies the cell to the right of it and pastes the result in Client Listings in next cell below.
    <LI>Repeats the step above for next Name on Sheet1 and pastes result below previous pasted name on Client Listings .[/list]I don't know how to tell it to stop the search because my code currently gets into a loop. It gives an error message that I assume that it can't find any more instances of Name .

    I realise that I require a Loop but am unsure of how to get the code to do the names and then move on to the addresses.

    Any suggestions?

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

    Re: Using Find for Data (VBA/Excel/2002-3)

    Does the FindNext method help? See the built-in VBA help or FindNext Method for a code example.

    If not, could you provide more detailed information? I don't quite understand what you mean by "finds the first instance of Name" - is this a specific name, or any name?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Find for Data (VBA/Excel/2002-3)

    Thanks Hans - Firstly I meant Name is a heading. It refers to a business name that is extracted from the finance app. There can be about 500 on each extracted sheet and I need to find each so that I can then copy the business name (next cell to right of heading Name )

    BTW, I did explore that code but my range is not defined. Sometimes it will be 5000 rows and other times it will be 3000 rows.

    I have tried UsedRange but it doesn't seem to define the range correctly. I couldn't get it to work with my code.

    As a test I did try the following code to see if I was getting closer but it also stops when it has run out of text to find and replace.

    <pre>Sub FindNext()
    With Worksheets(1).Range("a1:a5000")
    Set c = .Find("Name", LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Do
    c.Value = "FoundName"
    Set c = .FindNextŠ
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With
    End Sub</pre>


    At the mercy of the court...

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

    Re: Using Find for Data (VBA/Excel/2002-3)

    I'm afraid I still don't understand. Could you attach a small sample workbook with some dummy data and indicate what the end result should be?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Find for Data (VBA/Excel/2002-3)

    Sorry about the delay in replying but please offer suggestions re the code in the attached book.
    Attached Files Attached Files

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

    Re: Using Find for Data (VBA/Excel/2002-3)

    Try the code in the attached text file. It uses range objects to avoid selecting cells.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Find for Data (VBA/Excel/2002-3)

    Thanks Hans - I was able to use this code to do exactly what was needed. Now to go back and study what you did.

    The code was surprisingly fast even when I turned on screen updating. I'll have to learn more about it.

Posting Permissions

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