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
|