Merging Cells and inserting a /

[bigjaker]

I have a table that has 3 cells that need to be merged on one row and 2 cells on the next row that need to be merged as well. No problem doing the merge in VBA, but i need to go a step further. If the multiple cells contain information then I need to insert a "/" between the contents or where the cells were once separating the data.

In the 3 cells I might have a number in the first cell and nothing in the next 2 so I wouldn't need a "/". If there was info in the 1st and 3rd cell then I would need a "/" there to separate the 2. If there was info in the 2nd and 3rd then I would need a "/" between them, and finally if all 3 had info, you guessed it I will need a "/" between each piece of information.

I know how to do this in Excel but this project is in Word and needs to stay in Word. Any help or guidance would be appreciated.

Jake

[fumei]

Oh yuck. Do you really need merged cells.

Yes, you can merge cells with VBA, but are you aware that once you merge them, you can not - EVER - get anything out using VBA?

VBA will NOT do anything with merged cells. So once you merge them, that is it. VBA will never touch them again.

That aside, what have you got so far, for code?

It is not particularly difficult. Fussy, but not difficult. Essentually, you get the strings from the cells you want, test them for their content, and put the "/" in as required.

[bigjaker]

I know it would be so much easier to just turn on the borders on those cell splitting the info and be done with it.

It so far is very basic. So I can change stuff around a bit if needed to make it easier. I have one table that our database dumps info into a word document and then my VBA moves that around into various locations in the document in other tables. Would be easier if the database would put the info where needed but it won't. (long story).

I kind of figued that once they were merged that was going to be it and the verification would need to take place first.


Selection.GoTo What:=wdGoToTable, Which:=wdGoToNext, Count:=2, Name:=""
Selection.MoveRight Unit:=wdCell, Count:=12
Selection.MoveRight Unit:=wdCharacter, Count:=3, Extend:=wdExtend
Selection.Copy
Selection.MoveUp Unit:=wdLine, Count:=7
Selection.MoveRight Unit:=wdCell, Count:=2
Selection.Paste
Selection.MoveRight Unit:=wdCharacter, Count:=2, Extend:=wdExtend
Selection.Cells.Merge


Thanks Gerry for taking look.

Jake

[fumei]

First of all, there is likely no need to use Selection.

What are you doing?

It seems like you are:

1. go to the next table
2. moving the Selection 12 cells
3. extending the Selection 3 characters
4. copying the Selection
5. moving the Selection up 7 rows (???)
6. moving the Selection 2 cells over
7. paste

Yikes. Please describe precisely what you want. Your original post stated nothing about moving the text to a different row. I was assuming the cells to be merged are to be merged in the row they are in. Please clarify precisely what it is you want.

[fumei]

Is there some logic in determining which row is to be actioned?

[fumei]

Oh, and do you want a space being the existing text and the "/", or not.

12/yadda/blah

OR

12 / yadda / blah

This is why I ask for what you want precisely.

[fumei]

Oh, and does the table have more than 3 columns? If it has say 4 columns, are you only merging three of them?

[fumei]

Something like this? Click "Merge Me" on the top toolbar.

If the Selection in not in the table, there is a error message.

If the Selection is in a row with nothing in the cells, there is an error message.

If the Selection is in a row with text, your logic rules apply.


Here is a partial chunk of the code:
If Selection.Information(wdWithInTable) = True Then
Set aRow = Selection.Rows(1)
' set the three cell string variables
cellString1 = CellText(aRow.Cells(1).Range.Text)
cellString2 = CellText(aRow.Cells(2).Range.Text)
cellString3 = CellText(aRow.Cells(3).Range.Text)
' check to see if all blank
If cellString1 = "" And cellString2 = "" And _
cellString3 = "" Then
MsgBox "Row is blank."
Exit Sub
End If
' so now we know there IS text

' scenario 1 - 1,2,3 all have text
If cellString1 <> "" And cellString2 <> "" And _
cellString3 <> "" Then
OutputString = cellString1 & " / " & _
cellString2 & " / " & cellString3
Call MergeIt(aRow, OutputString)
End If

' scenario 2 - 1, 3 have text
If cellString1 <> "" And cellString2 = "" And _
cellString3 <> "" Then
OutputString = cellString1 & " / " & _
cellString3
Call MergeIt(aRow, OutputString)
End If

' scenario 3 - 2, 3 have text
If cellString1 = "" And cellString2 <> "" And _
cellString3 <> "" Then
OutputString = cellString2 & " / " & _
cellString3
Call MergeIt(aRow, OutputString)
End If

' scenario 4 - ONLY cell 1 has text
If cellString1 <> "" And cellString2 = "" And _
cellString3 = "" Then
OutputString = cellString1
Call MergeIt(aRow, OutputString)
End If


I am not posting the procedure MergeIt that does the actual merge and blending of text, and the project is locked. I am just trying to demonstrate (and clarify) what may be a possible solution.

[fumei]

Also I have not posted the Function CellText, which extracts ONLY the text string from a cell. Otherwise you get the end-of-cell marker. Which, in a merged cell becomes a paragraph mark (which it is really).

If you do not strip the end-of-cell marker, your text becomes multi-lined.

[bigjaker]

Gerry

Thanks. I am going to try to answer all your questions. We have a large word document that is populated from our database system. Most of the time the data can and will be placed into a word document where it belongs. However, this one time it doesn't and can't. The vendor said it can't be done. So what I have done for a work around is to get all the data into one table. Then I was going to use the macro to move the information from that table into the formatted tables on that page.

The table that from the database is one table with a header row that is 11 columns wide. By move right 12 columns it takes me to the first cell in row 2. I then would select the first 3 cells and move them to the previous table. (I know I should have it move back to the previous table using the goto tool and I will I was in the middle of developing my macro when I started to have questions).

I will test your code you sent to me shortly. Thank you that was very generous. I will let you know how it works.

Jake

[bigjaker]

Oh, and do you want a space being the existing text and the "/", or not.

12/yadda/blah

OR

12 / yadda / blah

This is why I ask for what you want precisely.

There should be no spaces, and the row is always row 2. What is needed is a merge of the first 3 cells in that row, and then the next 2 the way it is laid out.

Then I need to copy/move each of them to the previous table in the document. I have ready written the coding for the rest of the table that needs to be moved to another table.

[fumei]

" What is needed is a merge of the first 3 cells in that row, and then the next 2 the way it is laid out. "

Precisely please. I have no idea what "the way it is laid out" means.

You never mentioned it going to a previous table before. That is a rather significant piece of information.

Again, please state EXACTLY what you want to happen.

"By move right 12 columns it takes me to the first cell in row 2."

There is no need to do this. You can go to any cell you wanty directly, with NO movement of Selection.

"Then I need to copy/move each of them to the previous table in the document."

I have no idea what that means either. "each" of WHAT? The original cells?

"By move right 12 columns it takes me to the first cell in row 2. I then would select the first 3 cells and move them to the previous table. "

OK, so you are at Row2, Column1. You select the first three cells...and...

merge them in THIS table? THEN move them? Move the three cells to the previous than merge them there?

What?

[bigjaker]

" What is needed is a merge of the first 3 cells in that row, and then the next 2 the way it is laid out. "

Precisely please. I have no idea what "the way it is laid out" means.
Sorry I am trying to explain it clearly.

You never mentioned it going to a previous table before. That is a rather significant piece of information. I can do this part. This merge is only a small part of a much larger project. The merge is the part that is causing my headache.


Again, please state EXACTLY what you want to happen.

"By move right 12 columns it takes me to the first cell in row 2."

There is no need to do this. You can go to any cell you wanty directly, with NO movement of Selection.

Yes I know this, I just sent to you what I had so far, It wasn't cleaned up at all, it was just what the macro recorder gave to me. I saw what I was going to have a problem with and I hit VBAExpress because they have the most information. After a search I couldn't find what i was looking for so I reached out for help. You got to me quicker then I could clean up my coding. I am a newbie and have no training in VBA I am trying to self teach.


"Then I need to copy/move each of them to the previous table in the document."

I have no idea what that means either. "each" of WHAT? The original cells?

"By move right 12 columns it takes me to the first cell in row 2. I then would select the first 3 cells and move them to the previous table. "

OK, so you are at Row2, Column1. You select the first three cells...and...

merge them in THIS table? THEN move them? Move the three cells to the previous than merge them there?

I started to see through your questions that I would be best to change my thought process on this and how to do it more efficiently through your communications. Sorry I am not 100% clear so you can understand what I am trying to get help with. Excel is way easier. Way to many variables in word I guess.


I am guessing that the best thing to do is merge the table that is downloaded from the database system to do the merge there. I would only need to verify there was information in cells, Row 2 Columns 1, 2 & 3 for the first merge and then in the same row Columns 4 & 5 for the next merge. There are additional columns in the table that don't matter for the merge. Once the first 3 cells are merged, and the next 2 merged, i can set up the rest of the macro to move each of them to where they need to go and the rest of the table contents to it's location. I should be able to copy those cells and get them to paste where needed on my own.

I did get a chance to just play with the sheet you sent. Very cleaver, I would love to get to to that point one day when I just whip things like that out. The only problem I found with it was that there has to be something in column 1 to make it work. That is not always the case. Sometimes column 1 will be empty. But other then that perfect.

I uploaded a file that shows what i have on the page that this merge is taking place on. I didn't see this option earlier or I would have posted an example much sooner. The second table is from the database. The first table is where some of the information is going to end up after the merges are done.
Once again thank you for your time on this. I truly appreciate it. I hope I am getting closer to the point for you.

Jake

[bigjaker]

The document I attached is in Word 2007, but what ever we do, I need it to work in Word 2007 and Word 2003.

[fumei]

"The only problem I found with it was that there has to be something in column 1 to make it work. That is not always the case. Sometimes column 1 will be empty. But other then that perfect."

This is not correct. Did you actually try it? Put something in cell2 and cell3 - leaving cell 1 blank. Run the code. It works. I even posted the code:
' scenario 3 - 2, 3 have text
If cellString1 = "" And cellString2 <> "" And _
cellString3 <> "" Then
OutputString = cellString2 & " / " & _
cellString3
Call MergeIt(aRow, OutputString)
End If
I did miss the circumstance of only cell 2 though. Easy enough to fix.

I really do wish you would try to answer when I ask you to state EXACTLY (step-by-step) what you want to happen. I do not know, because you have not precisely stated, but you have stated enough that I will guess that, in the demo you attached:

In the previous table (Coverage Information) you want the second row - THREE cells (blank, "Liability", and blank) - to end up being ONE merged cell with "$500,000" (no "Liability").

Yes?

No?

If no....try stating - AGAIN - exactly, precisely, what you want to happen.

Last time I ask.

[bigjaker]

Table 1 shows what I get from our database.

Table 2 shows what I need it to look like when the merges are completed.

Table 3 is another variation from the database

Table 4 shows what it will look like when merged.

In row 2 I can have information in cells 1-5 in all cells or in any combination. There will always be at least one cell in the first 3 that has information. Cells 4 and 5 will always have at least one cell with information. If more then one cell in 1-3 has information I need a "/" to appear between the cell contents when merged. In cells 4 & 5 if both cells contain information I need a "/" between them when merged.

All I need is the merges to take place and place the "/" between cell contents as stated above.

[fumei]

Sorry, but I give up. I can do what I see in your document, but really, how hard is it to actually state what you want to happen. I keep asking.

In your latest document, you have an additional task with cells 4 and 5. This was never mentioned before. Why not just state, as I have asked, what you want to happen. All of it.

Further, you did not mention a previous table, then you mention doing something with a previous table, and now in your latest you do nothing with the previous table. I have no idea if this the truth, or not.

I wrote:


In the previous table (Coverage Information) you want the second row - THREE cells (blank, "Liability", and blank) - to end up being ONE merged cell with "$500,000" (no "Liability").

Yes?

No?

Did you answer yes, or no?????




Nope. You did not. So, I hope you can take what I have posted so far, and work with it. Or that someone else can help you.

[bigjaker]

Gerry,

Thanks for your help. I tried to answer everthing and make it as clear as possible. I even told you that the only thing I needed was a merge of the first 3 cells in the active table row with a "/" between cell contents if there were more then one cell containing information. Then the same thing for the next 2 cell in the same row. That is it. I can do the rest of the moving of the information on my own. The merge is the only part that i am having dificulty with.

Would have been handy if I could have seen the code you used in your password protected document. I might have been able to modify for my needs and posted a solved with the code used.

Jake

[fumei]

" I tried to answer everthing and make it as clear as possible. "

But you did NOT answer any direct questions. And I fail to see why you think you did.

I asked Yes? No? to the the guess about the previous table and what to do. Did you answer? Again, no you did not. If you think:

" I tried to answer everthing"

You may want to do a rethink about what "everything" means.

Here is the merging code you can use.

Sub MergeIt(aRange As Range, NewText As String)
Dim aCell As Cell
For Each aCell In aRange.Cells
aCell.Range.Text = ""
Next
aRange.Cells.Merge
aRange.Text = NewText
End Sub
To merge and replace the text:

1. grab each (original) cell text and append them together with your "/" - call the string OutputString.

2. make a range of the cells you want. For example:

Set r = aTable.Range.Cells(13).Range
r.MoveEnd Unit:=wdCell, Count:=2

sets the range object r to the first cell on row 2 (13th cell of the table), then move the range end two cells - thus making a range of the first THREE cells.

That range is passed to the MergeIt procedure, along with the appended string (OutputString).

Call MergeIt (r, OutputString)
You need to make the merged cell EMPTY first. That why there is:
For Each aCell In aRange.Cells
aCell.Range.Text = ""
Next
in the merging procedure.

[fumei]

Just to show what you seem to want. Click "Merge Tables" on toolbar. As you can see, Table2 looks like Table3 (as specified), - although you incorrectly name them Table 1 and Table 2, the first table is of course Table 1 - and Table 4 shows the same as Table 5.

[bigjaker]

Your macro that you posted works almost perfectly but I can't view it as the vba is still password protected. It only needs to do the merge in the active table not every table on the page.

Sorry I didn't answer with Yes and No responses. I thought more explanation was needed if you had questions as I thought it was a cut and dry problem/question I had.