Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a words in a column from a list of words.
Hi,
Not sure if this is possible, but hope someone can help. I'm trying to create a macro that will look at column A from row 10 to 100 for certain words. These words would be in a list, i.e. "Word One", "Word Two", "Word Three", "Word Four", "Word five", "Word six", but also this is not case sensitive, so it would look at the text if it was upper or lower case. Could these words be in an array or not, I'm not very good at writing macros so please bare with me. Then the cell that contains this word would make the same row but column D active, if you know what I mean. Hope someone can help, Thanks Best regards, Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a words in a column from a list of words.
Hi Scott,
This might help you get started at least. I'm not sure what you want to do when you find a match so now it just displays a message box with the row number of the match. Here goes: Add a commandbutton to the sheet and put this code in its click event: Dim astrList(5) As String Dim lngUpperBound As Long Dim strText As String Dim lngIndex As Long Dim lngRow As Long 'set the upperbound of the array '(number of words it'll contain) lngUpperBound = 5 'add words to the array astrList(0) = "boB" astrList(1) = "George" astrList(2) = "woRd" astrList(3) = "match?" astrList(4) = "YES" 'look for any matches in col1 For lngRow = 10 To 100 strText = UCase(ActiveSheet.Cells(lngRow, 1).Value) For lngIndex = 0 To lngUpperBound - 1 If UCase(astrList(lngIndex)) = strText Then 'match - do what you want here MsgBox "Found a word in row " & lngRow End If Next Next HTH, sivrik. -----Original Message----- Hi, Not sure if this is possible, but hope someone can help. I'm trying to create a macro that will look at column A from row 10 to 100 for certain words. These words would be in a list, i.e. "Word One", "Word Two", "Word Three", "Word Four", "Word five", "Word six", but also this is not case sensitive, so it would look at the text if it was upper or lower case. Could these words be in an array or not, I'm not very good at writing macros so please bare with me. Then the cell that contains this word would make the same row but column D active, if you know what I mean. Hope someone can help, Thanks Best regards, Scott . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a words in a column from a list of words.
Thanks Sivrik,
This works great, but one more question , is it possible to search a cell that contain the words i.e. instead of searching for a cell that only contains the word boB, search for a cell that contains boB that has other characters in the cell as. For example if a cell contains "Bob was here", then it would still pick this up. Hope you can make sense of all this and help, Thanks Best regards, Scott "sivrik" wrote in message ... Hi Scott, This might help you get started at least. I'm not sure what you want to do when you find a match so now it just displays a message box with the row number of the match. Here goes: Add a commandbutton to the sheet and put this code in its click event: Dim astrList(5) As String Dim lngUpperBound As Long Dim strText As String Dim lngIndex As Long Dim lngRow As Long 'set the upperbound of the array '(number of words it'll contain) lngUpperBound = 5 'add words to the array astrList(0) = "boB" astrList(1) = "George" astrList(2) = "woRd" astrList(3) = "match?" astrList(4) = "YES" 'look for any matches in col1 For lngRow = 10 To 100 strText = UCase(ActiveSheet.Cells(lngRow, 1).Value) For lngIndex = 0 To lngUpperBound - 1 If UCase(astrList(lngIndex)) = strText Then 'match - do what you want here MsgBox "Found a word in row " & lngRow End If Next Next HTH, sivrik. -----Original Message----- Hi, Not sure if this is possible, but hope someone can help. I'm trying to create a macro that will look at column A from row 10 to 100 for certain words. These words would be in a list, i.e. "Word One", "Word Two", "Word Three", "Word Four", "Word five", "Word six", but also this is not case sensitive, so it would look at the text if it was upper or lower case. Could these words be in an array or not, I'm not very good at writing macros so please bare with me. Then the cell that contains this word would make the same row but column D active, if you know what I mean. Hope someone can help, Thanks Best regards, Scott . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a words in a column from a list of words.
change
If UCase(astrList(lngIndex)) = strText Then to If Instr(1,strText,astrList(lngIndex),1) Then -- Regards, Tom Ogilvy "Scott" wrote in message ... Thanks Sivrik, This works great, but one more question , is it possible to search a cell that contain the words i.e. instead of searching for a cell that only contains the word boB, search for a cell that contains boB that has other characters in the cell as. For example if a cell contains "Bob was here", then it would still pick this up. Hope you can make sense of all this and help, Thanks Best regards, Scott "sivrik" wrote in message ... Hi Scott, This might help you get started at least. I'm not sure what you want to do when you find a match so now it just displays a message box with the row number of the match. Here goes: Add a commandbutton to the sheet and put this code in its click event: Dim astrList(5) As String Dim lngUpperBound As Long Dim strText As String Dim lngIndex As Long Dim lngRow As Long 'set the upperbound of the array '(number of words it'll contain) lngUpperBound = 5 'add words to the array astrList(0) = "boB" astrList(1) = "George" astrList(2) = "woRd" astrList(3) = "match?" astrList(4) = "YES" 'look for any matches in col1 For lngRow = 10 To 100 strText = UCase(ActiveSheet.Cells(lngRow, 1).Value) For lngIndex = 0 To lngUpperBound - 1 If UCase(astrList(lngIndex)) = strText Then 'match - do what you want here MsgBox "Found a word in row " & lngRow End If Next Next HTH, sivrik. -----Original Message----- Hi, Not sure if this is possible, but hope someone can help. I'm trying to create a macro that will look at column A from row 10 to 100 for certain words. These words would be in a list, i.e. "Word One", "Word Two", "Word Three", "Word Four", "Word five", "Word six", but also this is not case sensitive, so it would look at the text if it was upper or lower case. Could these words be in an array or not, I'm not very good at writing macros so please bare with me. Then the cell that contains this word would make the same row but column D active, if you know what I mean. Hope someone can help, Thanks Best regards, Scott . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a words in a column from a list of words.
Thanks very much Tom,
Maybe you can help me on my next problem. Now that it has found the cell with this word in, I need to select the cell in the same row in column D, and then create a SUM, in cell D120, that will add the value of D13 to the selected cell -1 (up 1 cell from the selected cell). I'm having a real problem with this, giving me a headache. Hope you can help, Thanks very much Best regards, Scott "Tom Ogilvy" wrote in message ... change If UCase(astrList(lngIndex)) = strText Then to If Instr(1,strText,astrList(lngIndex),1) Then -- Regards, Tom Ogilvy "Scott" wrote in message ... Thanks Sivrik, This works great, but one more question , is it possible to search a cell that contain the words i.e. instead of searching for a cell that only contains the word boB, search for a cell that contains boB that has other characters in the cell as. For example if a cell contains "Bob was here", then it would still pick this up. Hope you can make sense of all this and help, Thanks Best regards, Scott "sivrik" wrote in message ... Hi Scott, This might help you get started at least. I'm not sure what you want to do when you find a match so now it just displays a message box with the row number of the match. Here goes: Add a commandbutton to the sheet and put this code in its click event: Dim astrList(5) As String Dim lngUpperBound As Long Dim strText As String Dim lngIndex As Long Dim lngRow As Long 'set the upperbound of the array '(number of words it'll contain) lngUpperBound = 5 'add words to the array astrList(0) = "boB" astrList(1) = "George" astrList(2) = "woRd" astrList(3) = "match?" astrList(4) = "YES" 'look for any matches in col1 For lngRow = 10 To 100 strText = UCase(ActiveSheet.Cells(lngRow, 1).Value) For lngIndex = 0 To lngUpperBound - 1 If UCase(astrList(lngIndex)) = strText Then 'match - do what you want here MsgBox "Found a word in row " & lngRow End If Next Next HTH, sivrik. -----Original Message----- Hi, Not sure if this is possible, but hope someone can help. I'm trying to create a macro that will look at column A from row 10 to 100 for certain words. These words would be in a list, i.e. "Word One", "Word Two", "Word Three", "Word Four", "Word five", "Word six", but also this is not case sensitive, so it would look at the text if it was upper or lower case. Could these words be in an array or not, I'm not very good at writing macros so please bare with me. Then the cell that contains this word would make the same row but column D active, if you know what I mean. Hope someone can help, Thanks Best regards, Scott . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching for a words in a column from a list of words.
Dim astrList(5) As String
Dim lngUpperBound As Long Dim strText As String Dim lngIndex As Long Dim lngRow As Long 'set the upperbound of the array '(number of words it'll contain) lngUpperBound = 5 'add words to the array astrList(0) = "boB" astrList(1) = "George" astrList(2) = "woRd" astrList(3) = "match?" astrList(4) = "YES" 'look for any matches in col1 For lngRow = 10 To 100 strText = UCase(ActiveSheet.Cells(lngRow, 1).Value) For lngIndex = 0 To lngUpperBound - 1 If Instr(1,strText,astrList(lngIndex),1) Then 'match - do what you want here MsgBox "Found a word in row " & lngRow Range("D120").Value = Range("D13") +Cells(lngRow-1,4).Value End If Next Next As written, the value in D120 will be overwritten each time a row is found containing one of the words - but you didn't give any information on whether there could be multiple matches or not and if there are, what you want to do then. -- Regards, Tom Ogilvy "Scott" wrote in message ... Thanks very much Tom, Maybe you can help me on my next problem. Now that it has found the cell with this word in, I need to select the cell in the same row in column D, and then create a SUM, in cell D120, that will add the value of D13 to the selected cell -1 (up 1 cell from the selected cell). I'm having a real problem with this, giving me a headache. Hope you can help, Thanks very much Best regards, Scott "Tom Ogilvy" wrote in message ... change If UCase(astrList(lngIndex)) = strText Then to If Instr(1,strText,astrList(lngIndex),1) Then -- Regards, Tom Ogilvy "Scott" wrote in message ... Thanks Sivrik, This works great, but one more question , is it possible to search a cell that contain the words i.e. instead of searching for a cell that only contains the word boB, search for a cell that contains boB that has other characters in the cell as. For example if a cell contains "Bob was here", then it would still pick this up. Hope you can make sense of all this and help, Thanks Best regards, Scott "sivrik" wrote in message ... Hi Scott, This might help you get started at least. I'm not sure what you want to do when you find a match so now it just displays a message box with the row number of the match. Here goes: Add a commandbutton to the sheet and put this code in its click event: Dim astrList(5) As String Dim lngUpperBound As Long Dim strText As String Dim lngIndex As Long Dim lngRow As Long 'set the upperbound of the array '(number of words it'll contain) lngUpperBound = 5 'add words to the array astrList(0) = "boB" astrList(1) = "George" astrList(2) = "woRd" astrList(3) = "match?" astrList(4) = "YES" 'look for any matches in col1 For lngRow = 10 To 100 strText = UCase(ActiveSheet.Cells(lngRow, 1).Value) For lngIndex = 0 To lngUpperBound - 1 If UCase(astrList(lngIndex)) = strText Then 'match - do what you want here MsgBox "Found a word in row " & lngRow End If Next Next HTH, sivrik. -----Original Message----- Hi, Not sure if this is possible, but hope someone can help. I'm trying to create a macro that will look at column A from row 10 to 100 for certain words. These words would be in a list, i.e. "Word One", "Word Two", "Word Three", "Word Four", "Word five", "Word six", but also this is not case sensitive, so it would look at the text if it was upper or lower case. Could these words be in an array or not, I'm not very good at writing macros so please bare with me. Then the cell that contains this word would make the same row but column D active, if you know what I mean. Hope someone can help, Thanks Best regards, Scott . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to truncate list of meaningful words greater than 15 chars tomeaningful words of 8 chars. | Excel Worksheet Functions | |||
Can I have a list of reserved words in excel column name? | Excel Discussion (Misc queries) | |||
How to get a numbered list of unique words in a column? | Excel Discussion (Misc queries) | |||
Searching Text that contains particular WORDS. | Excel Worksheet Functions | |||
how do i insert words into a column without erasing the words | Excel Discussion (Misc queries) |