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]

This routine may do what you are asking for

[Code:]
Sub AddProp()
'ADD new property to all sheets
'In combobox1 we select a type, ie, the merged word in, usually, column A.
'On Error GoTo solveerror
Dim TypeName As String
Dim i As Integer
Dim mysearch As Variant

TypeName = "General Properties" '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)

If Not mysearch Is Nothing Then
Cells(mysearch.MergeArea(mysearch.MergeArea.Count).Row, mysearch.Column).EntireRow.Insert 'create a new row below
Application.DisplayAlerts = True
mysearch.MergeArea(mysearch.MergeArea.Count).Offset(-1, 1).FormulaR1C1 = "C4" 'Client
mysearch.MergeArea(mysearch.MergeArea.Count).Offset(-1, 2).FormulaR1C1 = 234 'TextBox1.Text 'write the value in front, in new row
mysearch.MergeArea(mysearch.MergeArea.Count).Offset(0, 2).Formula = "=Sum(R[-" & mysearch.MergeArea.Count & "]C:R[-1]C)"
End If
Next i
solveerror:
End Sub

[Code:/]