I have multiple ranges in various locations in the worksheet where I need to
merge cells in each range. The ranges are seperate and independant of each
other. Not merging into one big cell. Once the merge happens, a font is set
up and text is placed in each of the cells.

The problem is that all the ranges are merging, but the text is only placed
in the first range. I assume the problem is in how the code is telling where
to put the text.

Here is the code:


Sub Special_Terms_Booger()
Dim MyRange As Range, MyRange1 As Range, MyRange2 As Range
Set MyRange = Union(Range("M38:W42"), Range("M95:W99"))
Set MyRange1 = Union(Range("M152:W156"), Range("M209:W213"))
Set MyRange2 = Union(MyRange, MyRange1)
MyRange2.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
With ActiveCell.Characters(Start:=1, Length:=245).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
For Each cell In MyRange2
ActiveCell.FormulaR1C1 = _
"This is my text."
Next cell
End Sub

Replace the last 3 lines with

For Each cell In MyRange2
Cell = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Homer" wrote:

I have multiple ranges in various locations in the worksheet where I need to
merge cells in each range. The ranges are seperate and independant of each
other. Not merging into one big cell. Once the merge happens, a font is set
up and text is placed in each of the cells.

The problem is that all the ranges are merging, but the text is only placed
in the first range. I assume the problem is in how the code is telling where
to put the text.

Here is the code:


Sub Special_Terms_Booger()
Dim MyRange As Range, MyRange1 As Range, MyRange2 As Range
Set MyRange = Union(Range("M38:W42"), Range("M95:W99"))
Set MyRange1 = Union(Range("M152:W156"), Range("M209:W213"))
Set MyRange2 = Union(MyRange, MyRange1)
MyRange2.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
With ActiveCell.Characters(Start:=1, Length:=245).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
For Each cell In MyRange2
ActiveCell.FormulaR1C1 = _
"This is my text."
Next cell
End Sub

Correction

For Each Cell In myrange2
Cell.Value = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Replace the last 3 lines with

For Each cell In MyRange2
Cell = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Homer" wrote:

I have multiple ranges in various locations in the worksheet where I need to
merge cells in each range. The ranges are seperate and independant of each
other. Not merging into one big cell. Once the merge happens, a font is set
up and text is placed in each of the cells.

The problem is that all the ranges are merging, but the text is only placed
in the first range. I assume the problem is in how the code is telling where
to put the text.

Here is the code:


Sub Special_Terms_Booger()
Dim MyRange As Range, MyRange1 As Range, MyRange2 As Range
Set MyRange = Union(Range("M38:W42"), Range("M95:W99"))
Set MyRange1 = Union(Range("M152:W156"), Range("M209:W213"))
Set MyRange2 = Union(MyRange, MyRange1)
MyRange2.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
With ActiveCell.Characters(Start:=1, Length:=245).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
For Each cell In MyRange2
ActiveCell.FormulaR1C1 = _
"This is my text."
Next cell
End Sub

Thank you Jacob. The change works as needed.

I have another issue, not sure it is related to the original code below or
not.

There is a second module similar to that below, the only difference is that
it inserts different text. There is also a third that unmerges and fills in
the border.

What I have is a fourth module that calls either the first, second or third
based on a specific cell. When I run the fourth, it doesn't work. It is
placing the text from the first module in the cells for all ranges.

My guess is that it is something in the if and elseif statements.

Here is the code:

Sub Insert_Special_Terms()
If Range("W8").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W8").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
If Range("W65").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W65").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
If Range("W122").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W122").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
End Sub



"Jacob Skaria" wrote:

Correction

For Each Cell In myrange2
Cell.Value = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Replace the last 3 lines with

For Each cell In MyRange2
Cell = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Homer" wrote:

I have multiple ranges in various locations in the worksheet where I need to
merge cells in each range. The ranges are seperate and independant of each
other. Not merging into one big cell. Once the merge happens, a font is set
up and text is placed in each of the cells.

The problem is that all the ranges are merging, but the text is only placed
in the first range. I assume the problem is in how the code is telling where
to put the text.

Here is the code:


Sub Special_Terms_Booger()
Dim MyRange As Range, MyRange1 As Range, MyRange2 As Range
Set MyRange = Union(Range("M38:W42"), Range("M95:W99"))
Set MyRange1 = Union(Range("M152:W156"), Range("M209:W213"))
Set MyRange2 = Union(MyRange, MyRange1)
MyRange2.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
With ActiveCell.Characters(Start:=1, Length:=245).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
For Each cell In MyRange2
ActiveCell.FormulaR1C1 = _
"This is my text."
Next cell
End Sub

Correction:

It is not putting text from the first module in the cells. It is looping
through and the last If is what is taking over. That means what is placed in
the cells is based on the contents of range W122.


"Homer" wrote:

Thank you Jacob. The change works as needed.

I have another issue, not sure it is related to the original code below or
not.

There is a second module similar to that below, the only difference is that
it inserts different text. There is also a third that unmerges and fills in
the border.

What I have is a fourth module that calls either the first, second or third
based on a specific cell. When I run the fourth, it doesn't work. It is
placing the text from the first module in the cells for all ranges.

My guess is that it is something in the if and elseif statements.

Here is the code:

Sub Insert_Special_Terms()
If Range("W8").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W8").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
If Range("W65").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W65").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
If Range("W122").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W122").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
End Sub



"Jacob Skaria" wrote:

Correction

For Each Cell In myrange2
Cell.Value = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Replace the last 3 lines with

For Each cell In MyRange2
Cell = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Homer" wrote:

I have multiple ranges in various locations in the worksheet where I need to
merge cells in each range. The ranges are seperate and independant of each
other. Not merging into one big cell. Once the merge happens, a font is set
up and text is placed in each of the cells.

The problem is that all the ranges are merging, but the text is only placed
in the first range. I assume the problem is in how the code is telling where
to put the text.

Here is the code:


Sub Special_Terms_Booger()
Dim MyRange As Range, MyRange1 As Range, MyRange2 As Range
Set MyRange = Union(Range("M38:W42"), Range("M95:W99"))
Set MyRange1 = Union(Range("M152:W156"), Range("M209:W213"))
Set MyRange2 = Union(MyRange, MyRange1)
MyRange2.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
With ActiveCell.Characters(Start:=1, Length:=245).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
For Each cell In MyRange2
ActiveCell.FormulaR1C1 = _
"This is my text."
Next cell
End Sub

You can try the below two options

Sub Insert_Special_Terms()
If Range("W8").Value = Range("AH6").Value Or _
Range("W65").Value = Range("AH6").Value Or _
Range("W122").Value = Range("AH6").Value Then
Call Special_Terms_Booger
ElseIf Range("W8").Value = Range("AH7").Value Or _
Range("W65").Value = Range("AH7").Value Or _
Range("W122").Value = Range("AH7").Value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
End Sub

OR

Sub Insert_Special_Terms1()
If WorksheetFunction.CountIf(Range("W8,W65,W122"), Range("AH6")) Then
Call Special_Terms_Booger
ElseIf WorksheetFunction.CountIf(Range("W8,W65,W122"), Range("AH7")) Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Homer" wrote:

Correction:

It is not putting text from the first module in the cells. It is looping
through and the last If is what is taking over. That means what is placed in
the cells is based on the contents of range W122.


"Homer" wrote:

Thank you Jacob. The change works as needed.

I have another issue, not sure it is related to the original code below or
not.

There is a second module similar to that below, the only difference is that
it inserts different text. There is also a third that unmerges and fills in
the border.

What I have is a fourth module that calls either the first, second or third
based on a specific cell. When I run the fourth, it doesn't work. It is
placing the text from the first module in the cells for all ranges.

My guess is that it is something in the if and elseif statements.

Here is the code:

Sub Insert_Special_Terms()
If Range("W8").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W8").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
If Range("W65").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W65").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
If Range("W122").value = Range("AH6").value Then
Call Special_Terms_Booger
ElseIf Range("W122").value = Range("AH7").value Then
Call Special_Terms_Tree
Else
Call Delete_Special_Terms
End If
End Sub



"Jacob Skaria" wrote:

Correction

For Each Cell In myrange2
Cell.Value = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Replace the last 3 lines with

For Each cell In MyRange2
Cell = "This is my text."
Next

If this post helps click Yes
---------------
Jacob Skaria


"Homer" wrote:

I have multiple ranges in various locations in the worksheet where I need to
merge cells in each range. The ranges are seperate and independant of each
other. Not merging into one big cell. Once the merge happens, a font is set
up and text is placed in each of the cells.

The problem is that all the ranges are merging, but the text is only placed
in the first range. I assume the problem is in how the code is telling where
to put the text.

Here is the code:


Sub Special_Terms_Booger()
Dim MyRange As Range, MyRange1 As Range, MyRange2 As Range
Set MyRange = Union(Range("M38:W42"), Range("M95:W99"))
Set MyRange1 = Union(Range("M152:W156"), Range("M209:W213"))
Set MyRange2 = Union(MyRange, MyRange1)
MyRange2.Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
With ActiveCell.Characters(Start:=1, Length:=245).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
For Each cell In MyRange2
ActiveCell.FormulaR1C1 = _
"This is my text."
Next cell
End Sub