Text not looping through ranges

[Homer]

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

 

[Jacob Skaria]

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]

Correction

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

If this post helps click Yes

 

[Homer]

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

 

[Homer]

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.

 

[Jacob Skaria]

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

 

[Homer]

With the first option, if any of the three ranges = AH6 Special Terms Booger
is called. Each of the three ranges could equal AH6, AH7 or nothing.

With the second option, which looks like it will call the correct macro, I
get an error.

"Unable to get the CountIf property of the WorksheetFunction Class"

If we fix the error, I think we may have it.

Thanks for all your help.

 

[Jacob Skaria]

The first one is what you are looking for right?..In any case you need to
call the macro?

Sorry the second option do not work.

If this post helps click Yes

 

[Homer]

Not exactly what I'm looking for, but close. I'm sorry I haven't been very
clear with what I need.

If any of the ranges = AH6 I want to call Special Terms Booger.
If any of the ranges = AH6 I want to call Special Terms Tree.
If any of the ranges = anything else I want to call Delete Special Terms.

The problem comes if W8=AH6 and W65=AH7 and W122=something else. Special
Terms Booger is called for all three.

What I need is for each range to call out the macro based on what it equals.

 

[Jacob Skaria]

Use a boolean

Sub Insert_Special_Terms()
Dim blnPass As Boolean

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
blnPass = True
EndIf

If 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
blnPass = True
End If

'If the above two conditions didnt occur call the 3rd
If blnPass = False Then Call Delete_Special_Terms


End Sub

If this post helps click Yes

 

[Homer]

After playing with the codes this weekend, I found that the problem is not
with the codes you have suggested, but with the code that is being called by
each case.

There are three modules being called based on the contents of a cell. There
are 13 different cells that may contain differing information.

I'm going to need to go back and re-work some things.

Thanks for all your help.