Find Text using VBA

  • Thread starter Thread starter TIMOTHY
  • Start date Start date
T

TIMOTHY

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?
 
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?
Click to expand...

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-downlist. 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 codeso that when a values is found it will copy it to a column elswhere on theworksheet, 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
 
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?
Click to expand...


The last paragraph should read:

Additionally, just coloring the identified values in the master list may not serve your purpose. If you need a seperate 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.

H'wd
 
Hi Timothy,
This may be adaptable to your needs.  In the Named Range "Data" it willsearch 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 onthe 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
Click to expand...

thanks friend
 
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?
Click to expand...

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 (e-mail address removed)

Regards,
Howard
 
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
 
Back
Top