| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro - reads cells in a column .If keyword found moves cell conte
Let's say i have a column with lots of cells . Some containt text , some are
empty . Macro should read all the cells . If the given word/character is found , it moves the content in B column . B column should not have empty cells Example : Keyword : mother A1 : mother and father A2 empty cell A3 : mother A4 : son A5 : empty cell A6 : mother and nice The results in B column should be : B1 : mother and father B2 : mother B3 : mother and nice |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro - reads cells in a column .If keyword found moves cell conte
Hi,
Try this Sub Versive() x = 1 lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(UCase(c.Value), "MOTHER") 0 Then Cells(x, 2) = c.Value x = x + 1 End If Next End Sub Mike "andrei" wrote: Let's say i have a column with lots of cells . Some containt text , some are empty . Macro should read all the cells . If the given word/character is found , it moves the content in B column . B column should not have empty cells Example : Keyword : mother A1 : mother and father A2 empty cell A3 : mother A4 : son A5 : empty cell A6 : mother and nice The results in B column should be : B1 : mother and father B2 : mother B3 : mother and nice |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro - reads cells in a column .If keyword found moves cell c
Many thanks , it works !
"Mike H" wrote: Hi, Try this Sub Versive() x = 1 lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(UCase(c.Value), "MOTHER") 0 Then Cells(x, 2) = c.Value x = x + 1 End If Next End Sub Mike "andrei" wrote: Let's say i have a column with lots of cells . Some containt text , some are empty . Macro should read all the cells . If the given word/character is found , it moves the content in B column . B column should not have empty cells Example : Keyword : mother A1 : mother and father A2 empty cell A3 : mother A4 : son A5 : empty cell A6 : mother and nice The results in B column should be : B1 : mother and father B2 : mother B3 : mother and nice |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro - reads cells in a column .If keyword found moves cell conte
Instr() has its own comparison parm that you can use to ignore case differences:
If InStr(1, c.Value, "MOTHER", vbtextcompare) 0 Then Mike H wrote: Hi, Try this Sub Versive() x = 1 lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(UCase(c.Value), "MOTHER") 0 Then Cells(x, 2) = c.Value x = x + 1 End If Next End Sub Mike "andrei" wrote: Let's say i have a column with lots of cells . Some containt text , some are empty . Macro should read all the cells . If the given word/character is found , it moves the content in B column . B column should not have empty cells Example : Keyword : mother A1 : mother and father A2 empty cell A3 : mother A4 : son A5 : empty cell A6 : mother and nice The results in B column should be : B1 : mother and father B2 : mother B3 : mother and nice -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro - reads cells in a column .If keyword found moves cell c
Glad I could help and thanks for the feedback
"andrei" wrote: Many thanks , it works ! "Mike H" wrote: Hi, Try this Sub Versive() x = 1 lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(UCase(c.Value), "MOTHER") 0 Then Cells(x, 2) = c.Value x = x + 1 End If Next End Sub Mike "andrei" wrote: Let's say i have a column with lots of cells . Some containt text , some are empty . Macro should read all the cells . If the given word/character is found , it moves the content in B column . B column should not have empty cells Example : Keyword : mother A1 : mother and father A2 empty cell A3 : mother A4 : son A5 : empty cell A6 : mother and nice The results in B column should be : B1 : mother and father B2 : mother B3 : mother and nice |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro - reads cells in a column .If keyword found moves cell c
thanks for the tip
"Dave Peterson" wrote: Instr() has its own comparison parm that you can use to ignore case differences: If InStr(1, c.Value, "MOTHER", vbtextcompare) 0 Then Mike H wrote: Hi, Try this Sub Versive() x = 1 lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & lastrow) For Each c In MyRange If InStr(UCase(c.Value), "MOTHER") 0 Then Cells(x, 2) = c.Value x = x + 1 End If Next End Sub Mike "andrei" wrote: Let's say i have a column with lots of cells . Some containt text , some are empty . Macro should read all the cells . If the given word/character is found , it moves the content in B column . B column should not have empty cells Example : Keyword : mother A1 : mother and father A2 empty cell A3 : mother A4 : son A5 : empty cell A6 : mother and nice The results in B column should be : B1 : mother and father B2 : mother B3 : mother and nice -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro searches for keyword. If found , puts 0 in cell from next co | Excel Programming | |||
| Retain cell references when column moves | Charts and Charting in Excel | |||
| Populate a cell based on a keyword it found in another | Excel Programming | |||
| Populate a cell based on a keyword it found in another | Excel Programming | |||
| Formula to identify a keyword in all cells of a column | Excel Discussion (Misc queries) | |||
Linear Mode
