Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
Excel gurus... I have a spreadsheet that in Column C has descriptive data all in text form. The data might say something like "Paul is an Italian basketball player." What I need is for a macro to recognize the word "Italian" and place the word "Italy" in Column E to the right of the data column. Then I'd like to have another macro to recognize "basketball" and place "basketball" in Column F. However, sometimes there won't be information for Column E, in which case the data in Column F needs to move over to Column E if E is blank. And it needs to run for the entire set of rows. Is this possible? Many thanks in advance. Sandeman -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=522153 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
Sub ProcessWords()
Dim v as Variant, v1 as Variant Dim rng as Range, cell as Range Dim i as Long, col as long v = Array("Italian","BasketBall") v1 = Array("Italy","BasketBall) set rng = Range(Cells(1,3),Cells(rows.count,3).End(xlup)) for each cell in rng col = 4 for i = lbound(v) to ubound(v) if Instr(1,cell.Value,v(i).vbTextCompare) then cells(cell.row,col).Value = v1(i) col = col + 1 end if Next Next End Sub Modify v to include all the key words and v1 to include the words you want placed to the right if a keyword is found Code is untested and may contain typos, but should suggest a workable approach -- Regards, Tom Ogilvy "Sandeman" wrote: Excel gurus... I have a spreadsheet that in Column C has descriptive data all in text form. The data might say something like "Paul is an Italian basketball player." What I need is for a macro to recognize the word "Italian" and place the word "Italy" in Column E to the right of the data column. Then I'd like to have another macro to recognize "basketball" and place "basketball" in Column F. However, sometimes there won't be information for Column E, in which case the data in Column F needs to move over to Column E if E is blank. And it needs to run for the entire set of rows. Is this possible? Many thanks in advance. Sandeman -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=522153 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
Very cool Tom. Thank you. The If, Then line is not working out and I'm not sure what's wrong with it. Any ideas? Tom Ogilvy Wrote: Sub ProcessWords() Dim v as Variant, v1 as Variant Dim rng as Range, cell as Range Dim i as Long, col as long v = Array("Italian","BasketBall") v1 = Array("Italy","BasketBall) set rng = Range(Cells(1,3),Cells(rows.count,3).End(xlup)) for each cell in rng col = 4 for i = lbound(v) to ubound(v) IF INSTR(1,CELL.VALUE,V(I).VBTEXTCOMPARE) THEN cells(cell.row,col).Value = v1(i) col = col + 1 end if Next Next End Sub Modify v to include all the key words and v1 to include the words you want placed to the right if a keyword is found Code is untested and may contain typos, but should suggest a workable approach -- Regards, Tom Ogilvy "Sandeman" wrote: Excel gurus... I have a spreadsheet that in Column C has descriptive data all in text form. The data might say something like "Paul is an Italian basketball player." What I need is for a macro to recognize the word "Italian" and place the word "Italy" in Column E to the right of the data column. Then I'd like to have another macro to recognize "basketball" and place "basketball" in Column F. However, sometimes there won't be information for Column E, in which case the data in Column F needs to move over to Column E if E is blank. And it needs to run for the entire set of rows. Is this possible? Many thanks in advance. Sandeman -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=522153 -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=522153 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
As I said, it my contain typos - in this case, it contained two. Here is a
tested version (based on your description). Sub ProcessWords() Dim v As Variant, v1 As Variant Dim rng As Range, cell As Range Dim i As Long, col As Long v = Array("Italian", "BasketBall") v1 = Array("Italy", "BasketBall") Set rng = Range(Cells(1, 3), _ Cells(Rows.Count, 3).End(xlUp)) For Each cell In rng col = 4 For i = LBound(v) To UBound(v) If InStr(1, cell.Value, v(i), _ vbTextCompare) Then Cells(cell.Row, col).Value = v1(i) col = col + 1 End If Next Next End Sub "Sandeman" wrote: Very cool Tom. Thank you. The If, Then line is not working out and I'm not sure what's wrong with it. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
Super. How you and others acquire this expertise, I'll never know. This is working very well. Now I've run into an unforeseen issue. If in the description I search for the word "Italian," the word Italy is placed in a cell to the right. If I also look for the word "Italy," the word Italy is then placed two cells to the right. That means "Italy" has been listed twice. Would it be possible to add code that states if "Italy" has been found once, skip the next variations of the word "Italy" and move on to the next group (e.g. basketball)? There should be come work around perhaps? Tom Ogilvy Wrote: As I said, it my contain typos - in this case, it contained two. Here is a tested version (based on your description). Sub ProcessWords() Dim v As Variant, v1 As Variant Dim rng As Range, cell As Range Dim i As Long, col As Long v = Array("Italian", "BasketBall") v1 = Array("Italy", "BasketBall") Set rng = Range(Cells(1, 3), _ Cells(Rows.Count, 3).End(xlUp)) For Each cell In rng col = 4 For i = LBound(v) To UBound(v) If InStr(1, cell.Value, v(i), _ vbTextCompare) Then Cells(cell.Row, col).Value = v1(i) col = col + 1 End If Next Next End Sub "Sandeman" wrote: Very cool Tom. Thank you. The If, Then line is not working out and I'm not sure what's wrong with it. Any ideas? -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=522153 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
Bump for help. Thank you -- Sandema ----------------------------------------------------------------------- Sandeman's Profile: http://www.excelforum.com/member.php...fo&userid=3244 View this thread: http://www.excelforum.com/showthread.php?threadid=52215 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
Bump for help. Thank you -- Sandema ----------------------------------------------------------------------- Sandeman's Profile: http://www.excelforum.com/member.php...fo&userid=3244 View this thread: http://www.excelforum.com/showthread.php?threadid=52215 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
One more bump -- Sandema ----------------------------------------------------------------------- Sandeman's Profile: http://www.excelforum.com/member.php...fo&userid=3244 View this thread: http://www.excelforum.com/showthread.php?threadid=52215 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
Looks as if I need to make some changes to the macro structure. What I need the following macro to do is the following. If the macro looks at a description and identifies the word "Italian", it places the word "Italy" in a cell three columns over (column is "nation"). If it also identifies the word "basketball," it places the word "Basketball" in a cell four columns over (column is for "sport"). There are multiple identifiers I want to use, but I can set that up as long as I get the gist on how to edit the following to do this. Thanks! Sub ProcessWords() Dim v As Variant, v1 As Variant Dim rng As Range, cell As Range Dim i As Long, col As Long v = Array("Italian", "BasketBall") v1 = Array("Italy", "BasketBall") Set rng = Range(Cells(1, 3), _ Cells(Rows.Count, 3).End(xlUp)) For Each cell In rng col = 4 For i = LBound(v) To UBound(v) If InStr(1, cell.Value, v(i), _ vbTextCompare) Then Cells(cell.Row, col).Value = v1(i) col = col + 1 End If Next Next End Sub -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=522153 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro idendifying keywords
Bump for help. -- Sandeman ------------------------------------------------------------------------ Sandeman's Profile: http://www.excelforum.com/member.php...o&userid=32440 View this thread: http://www.excelforum.com/showthread...hreadid=522153 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting text between two keywords | Excel Worksheet Functions | |||
where do i find a list of macro keywords | Excel Programming | |||
Excel VBA keywords | Excel Programming | |||
Searching for keywords | Excel Programming | |||
VBA Keywords | Excel Programming |