| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text not looping through ranges
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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 --------------- Jacob Skaria "Homer" wrote: 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" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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" wrote: 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 --------------- Jacob Skaria "Homer" wrote: 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" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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 --------------- Jacob Skaria "Homer" wrote: 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" wrote: 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 --------------- Jacob Skaria "Homer" wrote: 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" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Text not looping through ranges
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Homer" wrote: 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" wrote: 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 --------------- Jacob Skaria "Homer" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| looping thru named ranges | Excel Programming | |||
| Looping through ranges | Excel Programming | |||
| looping through an array of ranges | Excel Programming | |||
| Looping in VB with cell ranges | Excel Worksheet Functions | |||
| Looping through named ranges | Excel Programming | |||
Linear Mode
