How to insert a new line when we have merged cells?

[marlonsaveri]

Hi, I need find some word, for example, ABC in each sheet; then, create a new row to put new values. However, in column A, we have merged cells, for example:
(I will dots to symbolize the merged cells)
......|123|123|123
......|123|123|123
ABC|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123
EFG|123|123|123
......|123|123|123
......|123|123|123
Then, a code need make:
......|123|123|123
......|123|123|123
ABC|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123 (newline)

......|123|123|123
......|123|123|123
EFG|123|123|123
......|123|123|123
......|123|123|123
So I tried use that code to do it

tipo = "ABC"
 For i = 2 To Sheets.Count
Set busca = Worksheets(i).Cells.Find(what:=tipo, after:=Range("A1"), LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
If Not busca Is Nothing Then
Cells(busca.MergeArea(busca.MergeArea.Count).Row + 1, busca.Column).EntireRow.Insert
                    Set intervalo = Union(busca.MergeArea, busca.MergeArea(busca.MergeArea.Count).Offset(1, 0))
                    Application.DisplayAlerts = False
                    intervalo.Merge
                    Application.DisplayAlerts = True
                    busca.MergeArea(busca.MergeArea.Count).Offset(0, 1).FormulaR1C1 = TextBox1.Text

But it's doing:
......|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123 (newline)
ABC|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123
or
......|123|123|123
......|123|123|123
ABC|123|123|123
......|123|123|123
......|123|123|123
......|123|123|123 (newline)
......|123|123|123
......|123|123|123
EFG|123|123|123
......|123|123|123
......|123|123|123
What's wrong?

 

[jreedich]

Please explain this line of code
busca.MergeArea(busca.MergeArea.Count).Offset(0, 1).FormulaR1C1 = TextBox1.Text

?TextBox1.Text

Are you saying that column A is merged cell that look like this

ABC123123123

or

<TABLE style="WIDTH: 194pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=259 border=0><COLGROUP><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 2816" width=99><COL style="WIDTH: 60pt" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=80 height=20>abc</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=99>123</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=80>123</TD></TR></TBODY></TABLE>

 

[marlonsaveri]

In that line, I want put the value of textbox1 in the new row. For example, if textbox1.text = "345":

This:
<TABLE style="WIDTH: 194pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=259><COLGROUP><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 2816" width=99><COL style="WIDTH: 60pt" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=80>abc</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=99 align=right>123
123
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=80 align=right>123
123
</TD></TR></TBODY></TABLE>

becomes:

<TABLE style="WIDTH: 194pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=259><COLGROUP><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 2816" width=99><COL style="WIDTH: 60pt" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=80>
abc
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=99 align=right>123
123
345
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-LEFT-COLOR: windowtext; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=80 align=right>123
123

</TD></TR></TBODY></TABLE>

(how can I draw here? Underline means another row)

 

[jreedich]

Is the text box on a user form or is it on the spread sheet?

Also is that the entire routine you have posted???

 

[marlonsaveri]

Inside a userform. And the routine is more complex.

 

[marlonsaveri]

Unfortunately I can not attach from this computer.

The sheet is like this:

For example, we select "General properties" in combobox1 and write "Clients D" in textbox1. Then, we want a new row appears in line 35, with A35 merged inside "General properties", B34 = Total, B35 = "Clients D" and B36 = running (with A36 = properties, merged below)
This is the sub-routine:

Sub AddProp()
[COLOR=green]  'ADD new property to all sheets[/COLOR]
[COLOR=green]  'In combobox1 we select a type, ie, the merged word in, usually, column A.[/COLOR]
[COLOR=green]  'On Error GoTo solveerror[/COLOR]
    Dim  TypeName As String
    Dim i As Integer
    Dim myrange As Range
    Dim plan As Worksheet
 
    TypeName = ComboBox1.Text
    For i = 2 To Sheets.Count
            Set mysearch = Worksheets(i).Cells.Find(what:=TypeName, after:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False) [COLOR=green]'find the word[/COLOR]
            If Not mysearch Is Nothing Then
                Cells(mysearch.MergeArea(mysearch.MergeArea.Count).Row + 1, mysearch.Column).EntireRow.Insert [COLOR=green]'create a new row below[/COLOR]
                Set myrange = Union(mysearch.MergeArea, mysearch.MergeArea(mysearch.MergeArea.Count).Offset(1, 0))
                Application.DisplayAlerts = False
                myrange.Merge [COLOR=green]'merge mysearch with new cell (in the new row)[/COLOR]
                Application.DisplayAlerts = True
                mysearch.MergeArea(mysearch.MergeArea.Count).Offset(0, 1).FormulaR1C1 = TextBox1.Text [COLOR=green]'write the value in front, in new row[/COLOR]
                mysearch.CurrentRegion.Borders(xlEdgeTop).Weight = xlThin [COLOR=green]'how to put all borders at once?[/COLOR]
            End If
    Next i
solveerror:
End Sub

 

[marlonsaveri]

I think the error is in:

Cells(busca.MergeArea(busca.MergeArea.Count).Row + 1, busca.Column).EntireRow.Insert

Because it seems to me that new row is not being created in the right position.
In the sample, I need create a new row between 34 and 35, them merged the new A35 with A34, but...

 

[jreedich]

Your error is in these lines

myrange.Activate
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

because of this you will insert a cell at A34 in column A

myrange is equal to $A$30:$A$34 in your code

Myrange needs to be equal to $A$34 I.E.(Range("A34").Activate)

 

[jreedich]

Sorry I had to stop working on your problem. Have you got an answer yet?

 

[kpark91]

Honestely, excel is terrible at dealing with merged cells.
It would be much better if you use center across selection from format cells