ExcelBanter - Find Text using VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Find Text using VBA (https://www.excelbanter.com/excel-programming/447193-find-text-using-vba.html)

Find Text using VBA

Dear All

I've a vast database of movies & Musics titles and i want to update the price

i have to find each text in database and stocklist since i cant vlookup it because same title can be manufactured by many other also

is there a way to find text automatically using VBA?

Find Text using VBA

On Friday, September 21, 2012 8:15:41 AM UTC-7, TIMOTHY wrote:
Dear All

I've a vast database of movies & Musics titles and i want to update the price

i have to find each text in database and stocklist since i cant vlookup it because same title can be manufactured by many other also

is there a way to find text automatically using VBA?

Hi Timothy,
This may be adaptable to your needs. In the Named Range "Data" it will search for the value you have entered into Range("G1") and upon finding that value will color the cell and move on to the next.

A couple of things... the value in G1 can be in a Data Validation drop-down list. Seems like you have a huge list to search so you may need to use more than one drop-down list. Perhaps three or four to cover the various major catigories of the entire list would be more managable. Would have to Dim additional Strings for each of the other drop-down cells.
Notice “i” is Dimmed as String and is set to Range("G1"), (Sorta falls in place with your other post asking about Dim etc.)

Additionally, just coloring the identified values in the values may not serve your purpose. If you need a list of those values we can modify the code so that when a values is found it will copy it to a column elswhere on the worksheet, then you can print it out or whatever suits.

Option Explicit
Sub PickEm()
Dim c As Range
Dim Data As Range
Dim i As String
Dim ans As String
i = Range("G1").Value

For Each c In Range("Data")
If c.Value = i Then c.Interior.ColorIndex = 24 ' 3, 20, 24
Next

ans = MsgBox("Clear color selection?", vbYesNo, "Yellar")
Select Case ans
Case vbYes
Range("Data").Interior.ColorIndex = xlNone
Case vbNo
Exit Sub
End Select
End Sub

HTH
Regards,
Howard

Find Text using VBA

On Sep 22, 4:03*am, Howard wrote:
On Friday, September 21, 2012 8:15:41 AM UTC-7, TIMOTHY wrote:
Dear All

I've a vast database of movies & Musics titles and i want to update the price

i have to find each text in database and stocklist since i cant vlookup it because same title can be manufactured by many other also

is there a way to find text automatically using VBA?

Hi Timothy,
This may be adaptable to your needs. *In the Named Range "Data" it will search for the value you have entered into Range("G1") and upon finding that value will color the cell and move on to the next.

A couple of things... the value in G1 can be in a Data Validation drop-down list. *Seems like you have a huge list to search so you may need to use more than one drop-down list. *Perhaps three or four to cover the various major catigories of the entire list would be more managable. *Would have to Dim additional Strings for each of the other drop-down cells.
Notice “i” is Dimmed as String and is set to Range("G1"), * (Sorta falls in place with your other post asking about Dim etc.)

Additionally, just coloring the identified values in the values may not serve your purpose. *If you need a list of those values we can modify the code so that when a values is found it will copy it to a column elswhere on the worksheet, then you can print it out or whatever suits.

Option Explicit
Sub PickEm()
Dim c As Range
Dim Data As Range
Dim i As String
Dim ans As String
i = Range("G1").Value

For Each c In Range("Data")
*If c.Value = i Then c.Interior.ColorIndex = 24 ' 3, 20, 24
Next

ans = MsgBox("Clear color selection?", vbYesNo, "Yellar")
Select Case ans
* Case vbYes
* * Range("Data").Interior.ColorIndex = xlNone
* Case vbNo
* * Exit Sub
End Select
End Sub

HTH
Regards,
Howard

thanks friend

Find Text using VBA

On Friday, September 21, 2012 8:15:41 AM UTC-7, TIMOTHY wrote:
Dear All

I've a vast database of movies & Musics titles and i want to update the price

i have to find each text in database and stocklist since i cant vlookup it because same title can be manufactured by many other also

is there a way to find text automatically using VBA?

Hi Timothy,

I continued to develop the code I submitted earlier and may have a worksheet example that you can adapt to more suit your purpose.

The sheet uses four drop-downs and will either list the drop-down entry OR color the matching cell of the master data base. I took the liberty to make identical master data entries unique but still identifable as to what movie or musical title it is. You could have four AA11 titles which could be the National Athem. AA11-1 is by Whitney Houston, AA11-2,3 or 4 by Curly, Larry, Moe.

Now that the titles are unique I used an Array Entered Vlookup formula to return Adress, City, State and Misc info for each from a data base.

Of course all my data and titles etc. are phony and only to show what the process is and how the formula and code can work.

Might be worth a look see and if it is way off-base for your purpose... well that is what the Delet Key is for.

If any interest I'm here

Regards,
Howard

Find Text using VBA

If it is so large (how large?) then it might be worth
it to move it to a real database, so you can use SQL to
query and update the data. It will take some time to move
over, but it probably will pay off in the long run.

RBS

"TIMOTHY" wrote in message
...
Dear All

I've a vast database of movies & Musics titles and i want to update the
price

i have to find each text in database and stocklist since i cant vlookup it
because same title can be manufactured by many other also

is there a way to find text automatically using VBA?