Creating a Macro to Find Text and Then Merge Cells [Solved/Closed]

katie215 4 Posts Monday May 19, 2014Registration date May 28, 2014 Last seen - May 19, 2014 at 10:39 AM - Latest reply: TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen
- Jun 2, 2014 at 12:26 PM
Hi-

I need some help with creating a macro. I haven't been able to get it to record correctly and I am completely illiterate in Visual Basic.

So what I need the macro to do is to find specific text, select the cell with that text, and then merge it with the 3 blank cells to the right of it (so that the footer isn't mushed into column A, but spread out on one line). There are 3 seperate lines of footers I need this to happen with. I've tried recording it but using CTRL F and finding the cell that way doesn't seem to stick in the macro (the macro just selects the same line as before when I try to run it in a different report).

The reason I need it to find the cell with the text first is that these are footers, and are in hundreds of reports that I need to re-format, and in each report the footers are on different rows, so I can't just create the macro to go to a specific row since it changes from report to report.


Any help you can give is greatly appreciated!!

Thanks,
Katie
See more 

6 replies

Best answer
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - May 19, 2014 at 12:03 PM
3
Thank you
Hi Katie,

It clear to me what you want to do but unclear why.

1) A footer is always placed at the bottom of a document, which means a fixed position.
2) If the 3 cells to the right are empty, then the text in the first cell will be visible.

Anyway, the following code will go through column A looking for the 3 phrases and merging the next 3 cells to the right of the phrase found:
Sub RunMe()
Dim lRow As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A1:A" & lRow)
    If cell = "Other grades include" _
    Or cell = "Data reflect grades posted as of" _
    Or cell = "Report produced by" Then
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Merge
    End If
Next cell
End Sub


Best regards,
Trowa

Thank you, TrowaD 3

Something to say? Add comment

CCM has helped 1669 users this month

katie215 4 Posts Monday May 19, 2014Registration date May 28, 2014 Last seen - May 19, 2014 at 12:13 PM
0
Thank you
Hi Trowa- that worked! Thank you!

In regards to your 2 comments, I have hundreds of reports where these footers are at the bottom of the document, but the specific row changes as each report is a different length. And when the report is created (run from an automated program) the footer text doesn't span the columns, it just jams up into the cell in column A (the text wraps) so wanted to get it to spread out.

Thanks so much again for your help- I greatly appreciate it!

Katie
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - May 20, 2014 at 10:40 AM
Thanks for the clarification Katie and I am glad to be able to help you.

See you
katie215 4 Posts Monday May 19, 2014Registration date May 28, 2014 Last seen - May 28, 2014 at 01:34 PM
0
Thank you
Hi-

I was wondering if you could help me modify this code slightly. Is there a way to say 'contains' instead of equals when the macro is looking for the text? The text of the 3 different cells changes ever so slightly every few months so if I could say if the cell contains "other grades include" then do the merge, it would save me from having to update the code every few months.

Thanks for your help!!

Katie
katie215 4 Posts Monday May 19, 2014Registration date May 28, 2014 Last seen - May 28, 2014 at 02:21 PM
0
Thank you
Okay nevermind- I posted this on another forum as well and got an answer (to change the equals to 'like' and add wildcards * in my text)

This is such a huge help though- thanks so much for helping me through this!

Katie
TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Jun 2, 2014 at 12:26 PM
Thanks Katie for coming back to post your answer.