Solved: Finding a string in Excel

[deyken]

Hi There,

Is there a reasonably easy function/method that I can use in code to locate a particular string in a large Excel spreadsheet.

At the same time, I need to perform this function to begin a loop iteration at the row directly after this string ocurs. Can I then use ActiveCell.Row to locate the row number of this string, once found?

Finally, is there a way I can put this Macro of mine into a globally accessible area, like the Quickaccess Toolbar? This macro iterates through several hundred rows of data and uploads it to a SQL Server Database on our work network. I want a few users to be able to use this Macro, without now having to build this macro into every new spreadsheet they need to work with...

Any help appreciated!

[mikerickson]

Have you looked at the built in Find feature?

[deyken]

Hi Mike

I did not. What would be the syntax for that - it requests an arsenal parameters, which I haven't worked with before.

I want to locate a particular literal string ("SIMPLE TOTALS ") in a cell that might be located at different Row indexes. Essentially I want a little UDF to return the line number where the string ocurs.

Here is the code I have tried so far:
Function GetStopLine()
Dim RowStart As Integer
RowStart = 1

Do Until Sheet1.Cells(RowStart, "C") = "SIMPLE TOTALS "
RowStart = RowStart + 1
Loop

GetStopLine = RowStart
End Function

[mikerickson]

Its not VBA, its native Excel.

Press Command+F on a Mac.
Its on the right of the Home Ribbon on Windows versions, if I remember correctly.
Excel Help will guide you on the specifics for your version of Excel.

[deyken]

Thanks! Using the native Excel function worked fine.

[Brady]

you can select all columns in work sheet then press Ctrl+H to replace and then in Find What bar write abc and in replace with bar write nothing and press ok all abc will be vanished from the work sheet ....

It depends that are you talking that entire cells have this name like A1=apple , B5=apple then above command will easily blank the cells ....

But it you are talking that text any where in the cells then there will be need of a macro like you want to say cell contain an apple , good apple then you want whole cell to be blank then this command will not work as per your needs .... more over this command will replace all apple even if there are applet then it will remove apple and only t will be remaining .....