Need macro that transforms key words into predefined numbers
Let's say i have a column with the following cells :
A1 : house A2 : garden A3 : room A4 : garden A5: bedroom A6 : garden A7 : bedroom I need a macro which transforms the words into predefined numbers . So the results in column B should be like this B1 : 1 B2 : 2 B3 : 3 B4 : 2 B5 : 4 B6 : 2 B7 : 4 Most likely , in my case , the cells contain only one word . What about if a cell contains the key word and other words as well ( words which are not key words ) . Something like this : A1 : My house is a cottage A2 : What a beautiful garden A3 : Go to your room A4 : I sold my garden yesterday A5: I need another bedroom A6 : I like my garden very much A7 : Go to your bedroom and wash the floor I would like the result to be the same as in the first case . A1 : house A2 : garden A3 : room A4 : garden A5: bedroom A6 : garden A7 : bedroom Can this be done ? |
Need macro that transforms key words into predefined numbers
This macro should do what you want. Just set the constants in the three
Const statements to the values you want. Note that the WorkList constant must be a comma delimited list of words or phrases... do *not* put any spaces around the commas to "neaten" things up. Sub DecodeWords() Dim X As Long, Z As Long, LastRow As Long, Words() As String Const StartRow As Long = 1 Const DataColumn As String = "A" Const WordList As String = "house,garden,room,bedroom" LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row Words = Split(LCase(WordList), ",") For X = StartRow To LastRow For Z = 0 To UBound(Words) If " " & LCase(Cells(X, DataColumn).Value) & " " _ Like "*[!a-z]" & Words(Z) & "[!a-z]*" Then Cells(X, DataColumn).Offset(0, 1).Value = Z + 1 Exit For End If Next Next End Sub -- Rick (MVP - Excel) "andrei" wrote in message ... Let's say i have a column with the following cells : A1 : house A2 : garden A3 : room A4 : garden A5: bedroom A6 : garden A7 : bedroom I need a macro which transforms the words into predefined numbers . So the results in column B should be like this B1 : 1 B2 : 2 B3 : 3 B4 : 2 B5 : 4 B6 : 2 B7 : 4 Most likely , in my case , the cells contain only one word . What about if a cell contains the key word and other words as well ( words which are not key words ) . Something like this : A1 : My house is a cottage A2 : What a beautiful garden A3 : Go to your room A4 : I sold my garden yesterday A5: I need another bedroom A6 : I like my garden very much A7 : Go to your bedroom and wash the floor I would like the result to be the same as in the first case . A1 : house A2 : garden A3 : room A4 : garden A5: bedroom A6 : garden A7 : bedroom Can this be done ? |
Need macro that transforms key words into predefined numbers
It works . Thank you very much
"Rick Rothstein" wrote: This macro should do what you want. Just set the constants in the three Const statements to the values you want. Note that the WorkList constant must be a comma delimited list of words or phrases... do *not* put any spaces around the commas to "neaten" things up. Sub DecodeWords() Dim X As Long, Z As Long, LastRow As Long, Words() As String Const StartRow As Long = 1 Const DataColumn As String = "A" Const WordList As String = "house,garden,room,bedroom" LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row Words = Split(LCase(WordList), ",") For X = StartRow To LastRow For Z = 0 To UBound(Words) If " " & LCase(Cells(X, DataColumn).Value) & " " _ Like "*[!a-z]" & Words(Z) & "[!a-z]*" Then Cells(X, DataColumn).Offset(0, 1).Value = Z + 1 Exit For End If Next Next End Sub -- Rick (MVP - Excel) "andrei" wrote in message ... Let's say i have a column with the following cells : A1 : house A2 : garden A3 : room A4 : garden A5: bedroom A6 : garden A7 : bedroom I need a macro which transforms the words into predefined numbers . So the results in column B should be like this B1 : 1 B2 : 2 B3 : 3 B4 : 2 B5 : 4 B6 : 2 B7 : 4 Most likely , in my case , the cells contain only one word . What about if a cell contains the key word and other words as well ( words which are not key words ) . Something like this : A1 : My house is a cottage A2 : What a beautiful garden A3 : Go to your room A4 : I sold my garden yesterday A5: I need another bedroom A6 : I like my garden very much A7 : Go to your bedroom and wash the floor I would like the result to be the same as in the first case . A1 : house A2 : garden A3 : room A4 : garden A5: bedroom A6 : garden A7 : bedroom Can this be done ? |
All times are GMT +1. The time now is 02:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com