Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
open some txt files ,find text , copy the text before that to a single cell | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |