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

Question

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

TrowaD · Accepted Answer

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

katie215 · Answer

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

katie215 · Answer

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 · Answer

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

Creating a Macro to Find Text and Then Merge Cells

4 responses

Similar discussions

Subscribe To Our Newsletter!