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

Topic

New Reply

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:

• Creates a new sheet Client Listings
• Creates headings in cells A3, B3, C3 and D3 – Name , Address , Phone Number , Mobile Number
• 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.
• 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?

Reply | Quote

Replies

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?

Reply | Quote

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.

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

At the mercy of the court…

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote