combining the text of several column

[kky2k]

Hello,

How do I go about combining the test of several column. This is what
need. I have a very long excel file. Within this file, I need o
search for the word "menu". Once the word is located, I need to b
able to take the word within this cell and then combine it with th
word that is in the cell besides it. For example:

Column1 Column2 Column3 Column4
test menu test test
test test test test
test menu A B

What I would like is if the word menu was located on the first row, t
insert a row below and then put in the test menu as well as test an
test "menu test test" As for the 3rd row, put in the word "menu A B
on a row below.

I'm not sure how to go about doing this, so far, not having muc
success. I would really appreciate any help!

Thanks!!!

Ki

 

[Frank Kabel]

Hi
not totally sure but try the following macro

Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long
Dim menu_str

lastrow = ActiveSheet.Cells(Rows.count, "B").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "B").Value "menu" Then
Cells(row_index + 1, "B").EntireRow.Insert (xlShiftDown)
menu_str = "menu" & " " & Cells(row_index, "C").Value & _
" " & Cells(row_index, "D").Value
Cells(row_index + 1, "B").value = menu_str
End If
Next
End Sub

 

[kky2k]

HI Frank,

I try the code but it got stuck at the (xlUp) section. I attached par
of the document I'm trying to use the code for. On line 4 where th
word menu appear, I would like to combine the text on the entire row.


May you please help me out...any help would be appreciated!

Thank you!

Ki

Attachment filename: menu_con.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51707

 

[kky2k]

Hi Frank,

I made a typo with your codes, was able to get the macro running bu
after locating the first line, the macro stopped:

The codes I'm using:

Sub Merge()

Dim lastrow As Long
Dim row_index As Long
Dim menu_str As String


lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For row_index = lastrow - 1 To 1 Step -1

If Cells(row_index, "A").Value = "MENU TYPE" Then
Cells(row_index + 1, "A").EntireRow.Insert (xlShiftDown)
menu_str = "MENU TYPE" & " " & Cells(row_index, "B").Value & " " _
& Cells(row_index, "C").Value & " " & Cells(row_index, "D").Value _
& " " & Cells(row_index, "E").Value

Cells(row_index + 1, "A").Value = menu_str

End If

Next

End Sub

Anyway to have it search through the entire pages. The list i
actually around 60,000 rows long.

Thank you!

Ki

 

[Frank Kabel]

Hi
what do you mean it stoped?
Did it produce an error?