Basic help

  • Thread starter erikkeith via OfficeKB.com
  • Start date
E

erikkeith via OfficeKB.com

Is there a way to manipulate a column without having to refer to it every
time? For instance, can I somehow select the column by using M:M and then
reference the cell rows within that column of M?

Here is what I have:

Range("M12,M17,M23,M27,M31,M36,M41,M46,M51,M58,M64,M69,M73,M78,M84").Select
Selection.Interior.ColorIndex = 6
Range("M12").Select
If Range("AB" & sCell(0)).Value = "N" Then
ActiveCell.Value = sNames(0)
Else: ActiveCell = "Alternate"
End If

Here is what I want (after somehow selecting the column "M"):

Range("12,17,23,27,31,36,41,46,51,58,64,69,73,78,84").Select
Selection.Interior.ColorIndex = 6
Range("12").Select
If Range("AB" & sCell(0)).Value = "N" Then
ActiveCell.Value = sNames(0)
Else: ActiveCell = "Alternate"
End If
 
B

Bob Phillips

You could do this, although it hardly seems simpler

With Columns(13)
Union(.Cells(12), .Cells(17), .Cells(23), .Cells(27), _
.Cells(31), .Cells(36), .Cells(41), .Cells(46), _
.Cells(51), .Cells(58), .Cells(64), .Cells(69), _
.Cells(73), .Cells(78), .Cells(84)).Select
End With

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
E

erikkeith via OfficeKB.com

How can I use this continually referring to a cell within that column? I
have a macro set up to say, "If M:10 equals 12, 13, 13.2, 14 or 15 to run a
sub macro. I have this set up for columns M, N, O, P, Q, V and W so it is
big program (17,000+ lines of code). I am trying to condense it so what I
want to do is right a sub macro that does not have to refer to a specific
column each time. I would rather set up an If then statement to say
something like,
If M:10 equals 12 then
Market9_macro

In this Market9_macro is where I want to be able to not have a column
specified so I can use the same output regardless of what column I chose. i.
e. I want to be able to take my written code for columns M and N and condense
it into one using this theory. To clear this up I will copy the first 2
columns I have:

Dim sNames
sNames = Array("Ron", "Noe", "Adam", "Robert", "Brett", "Abe", "Jon",
"Christina", "Kevin A.", "Steve U.", "Steven J", "Joe", "Jack", "Patick",
"Frank")
Dim sCell
sCell = Array("20", "32", "30", "23", "35", "22", "38", "17", "37", "36",
"39", "26", "31", "29", "40")
Dim sAlternate
sAlternate = Array("Steve W.", "Drew", "Ren", "Andrew", "Jerry", "Billy",
"Kevin D.", "Chase", "Bryce", "Amy")
Dim sAcell
sAcell = Array("28", "24", "16", "25", "21", "27", "17", "34", "33", "19")

Dim sLead
sLead = Array("Rodger", "Stacy", "Erik")
Dim sLcell
sLcell = Array("13", "14", "15")
Dim sData
sData = Array("Shane", "Juan", "Phillip", "Noe", "Anthony")
Dim sDcell
sDcell = Array("42", "43", "44", "45", "46")
Range("M10").Select
If ActiveCell = "15" Then
Range("M90,M95:M102").Select
Selection.ClearContents
Range("M11:M89").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("M11:M89").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("M11").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Monday"
Range("M11,M16,M22,M26,M30,M35,M40,M45,M50,M57,M63,M68,M72,M77,M83").
Select
Range("M83").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("M12,M17,M23,M27,M31,M36,M41,M46,M51,M58,M64,M69,M73,M78,M84").
Select
Range("M84").Activate
Selection.Interior.ColorIndex = 6
Range("M12").Select
If Range("AB" & sCell(0)).Value = "N" Then
ActiveCell.Value = sNames(0)
Else: ActiveCell = "Alternate"
End If
Range("M17").Select
If Range("AB" & sCell(1)).Value = "N" Then
ActiveCell.Value = sNames(1)
Else: ActiveCell = "Alternate"
End If
Range("M23").Select
If Range("AB" & sCell(2)).Value = "N" Then
ActiveCell.Value = sNames(2)
Else: ActiveCell = "Alternate"
End If
Range("M27").Select
If Range("AB" & sCell(3)).Value = "N" Then
ActiveCell.Value = sNames(3)
Else: ActiveCell = "Alternate"
End If
Range("M31").Select
If Range("AB" & sCell(4)).Value = "N" Then
ActiveCell.Value = sNames(4)
Else: ActiveCell = "Alternate"
End If
Range("M36").Select
If Range("AB" & sCell(5)).Value = "N" Then
ActiveCell.Value = sNames(5)
Else: ActiveCell = "Alternate"
End If
Range("M41").Select
If Range("AB" & sCell(6)).Value = "N" Then
ActiveCell.Value = sNames(6)
Else: ActiveCell = "Alternate"
End If
Range("M46").Select
If Range("AB" & sCell(7)).Value = "N" Then
ActiveCell.Value = sNames(7)
Else: ActiveCell = "Alternate"
End If
Range("M51").Select
If Range("AB" & sCell(8)).Value = "N" Then
ActiveCell.Value = sNames(8)
Else: ActiveCell = "Alternate"
End If
Range("M58").Select
If Range("AB" & sCell(9)).Value = "N" Then
ActiveCell.Value = sNames(9)
Else: ActiveCell = "Alternate"
End If
Range("M64").Select
If Range("AB" & sCell(10)).Value = "N" Then
ActiveCell.Value = sNames(10)
Else: ActiveCell = "Alternate"
End If
Range("M69").Select
If Range("AB" & sCell(11)).Value = "N" Then
ActiveCell.Value = sNames(11)
Else: ActiveCell = "Alternate"
End If
Range("M73").Select
If Range("AB" & sCell(12)).Value = "N" Then
ActiveCell.Value = sNames(12)
Else: ActiveCell = "Alternate"
End If
Range("M78").Select
If Range("AB" & sCell(13)).Value = "N" Then
ActiveCell.Value = sNames(13)
ElseIf Range("AB" & sAcell(4)).Value = "N" Then
ActiveCell = sAlternate(4)
Selection.Interior.ColorIndex = 10
Else: ActiveCell = "Alternate"
End If
Range("M84").Select
If Range("AB" & sCell(14)).Value = "N" Then
ActiveCell.Value = sNames(14)
Else: ActiveCell = "Alternate"
End If
Range("M94").Select
If Range("AB" & sLcell(2)).Value = "N" Then
ActiveCell = sLead(2)
ElseIf Range("AB" & sLcell(1)).Value = "N" Then
ActiveCell = sLead(1)
ElseIf Range("AB" & sLcell(0)).Value = "N" Then
ActiveCell = sLead(0)
End If
Range("M95").Select
If Range("AB" & sLcell(1)).Value = "N" And Range("M94").Value <>
sLead(1) Then
ActiveCell = sLead(1)
End If
Range("M96").Select
If Range("AB" & sLcell(0)).Value = "N" And Range("M94").Value <>
sLead(0) Then
ActiveCell = sLead(0)
End If
End If

Range("N10").Select
If ActiveCell = "15" Then
Range("N90,N95:N102").Select
Selection.ClearContents
Range("N11:N89").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("N11:N89").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("N11").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Tuesday"
Range("N11,N16,N22,N26,N30,N35,N40,N45,N50,N57,N63,N68,N72,N77,N83").
Select
Range("N83").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("N12,N17,N23,N27,N31,N36,N41,N46,N51,N58,N64,N69,N73,N78,N84").
Select
Range("N84").Activate
Selection.Interior.ColorIndex = 6
Range("N12").Select
If Range("AC" & sCell(0)).Value = "N" Then
ActiveCell.Value = sNames(0)
Else: ActiveCell = "Alternate"
End If
Range("N17").Select
If Range("AC" & sCell(1)).Value = "N" Then
ActiveCell.Value = sNames(1)
ElseIf Range("AC" & sAcell(2)).Value = "N" Then
ActiveCell = sAlternate(2)
Selection.Interior.ColorIndex = 45
Else: ActiveCell = "Alternate"
End If
Range("N23").Select
If Range("AC" & sCell(2)).Value = "N" Then
ActiveCell.Value = sNames(2)
Else: ActiveCell = "Alternate"
End If
Range("N27").Select
If Range("AC" & sCell(3)).Value = "N" Then
ActiveCell.Value = sNames(3)
Else: ActiveCell = "Alternate"
End If
Range("N31").Select
If Range("AC" & sCell(4)).Value = "N" Then
ActiveCell.Value = sNames(4)
Else: ActiveCell = "Alternate"
End If
Range("N36").Select
If Range("AC" & sCell(5)).Value = "N" Then
ActiveCell.Value = sNames(5)
ElseIf Range("AC" & sAcell(0)).Value = "N" Then
ActiveCell = sAlternate(0)
Selection.Interior.ColorIndex = 55
Else: ActiveCell = "Alternate"
End If
Range("N41").Select
If Range("AC" & sCell(6)).Value = "N" Then
ActiveCell.Value = sNames(6)
Else: ActiveCell = "Alternate"
End If
Range("N46").Select
If Range("AC" & sCell(7)).Value = "N" Then
ActiveCell.Value = sNames(7)
Else: ActiveCell = "Alternate"
End If
Range("N51").Select
If Range("AC" & sCell(8)).Value = "N" Then
ActiveCell.Value = sNames(8)
Else: ActiveCell = "Alternate"
End If
Range("N58").Select
If Range("AC" & sCell(9)).Value = "N" Then
ActiveCell.Value = sNames(9)
Else: ActiveCell = "Alternate"
End If
Range("N64").Select
If Range("AC" & sCell(10)).Value = "N" Then
ActiveCell.Value = sNames(10)
Else: ActiveCell = "Alternate"
End If
Range("N69").Select
If Range("AC" & sCell(11)).Value = "N" Then
ActiveCell.Value = sNames(11)
Else: ActiveCell = "Alternate"
End If
Range("N73").Select
If Range("AC" & sCell(12)).Value = "N" Then
ActiveCell.Value = sNames(12)
ElseIf Range("AC" & sAcell(2)).Value = "N" Then
ActiveCell = sAlternate(2)
Selection.Interior.ColorIndex = 14
Else: ActiveCell = "Alternate"
End If
Range("N78").Select
If Range("AC" & sCell(13)).Value = "N" Then
ActiveCell.Value = sNames(13)
ElseIf Range("AC" & sAcell(4)).Value = "N" Then
ActiveCell = sAlternate(4)
Selection.Interior.ColorIndex = 10
Else: ActiveCell = "Alternate"
End If
Range("N84").Select
If Range("AC" & sCell(14)).Value = "N" Then
ActiveCell.Value = sNames(14)
Else: ActiveCell = "Alternate"
End If
Range("N94").Select
If Range("AC" & sLcell(2)).Value = "N" Then
ActiveCell = sLead(2)
ElseIf Range("AC" & sLcell(0)).Value = "N" Then
ActiveCell = sLead(0)
ElseIf Range("AC" & sLcell(1)).Value = "N" Then
ActiveCell = sLead(1)
End If
Range("N95").Select
If Range("AC" & sLcell(0)).Value = "N" And Range("N94").Value <>
sLead(0) Then
ActiveCell = sLead(0)
End If
Range("N96").Select
If Range("AC" & sLcell(1)).Value = "N" And Range("N94").Value <>
sLead(1) Then
ActiveCell = sLead(1)
End If
End If

Can you help? Or do I need more specifics?

Bob said:
You could do this, although it hardly seems simpler

With Columns(13)
Union(.Cells(12), .Cells(17), .Cells(23), .Cells(27), _
.Cells(31), .Cells(36), .Cells(41), .Cells(46), _
.Cells(51), .Cells(58), .Cells(64), .Cells(69), _
.Cells(73), .Cells(78), .Cells(84)).Select
End With

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Is there a way to manipulate a column without having to refer to it every
time? For instance, can I somehow select the column by using M:M and then
reference the cell rows within that column of M?

Here is what I have:
Range("M12,M17,M23,M27,M31,M36,M41,M46,M51,M58,M64,M69,M73,M78,M84").Select
Selection.Interior.ColorIndex = 6
Range("M12").Select
Click to expand...
[quoted text clipped - 16 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
 
B

Bob Phillips

I am not really sure what you are asking, but perhaps this will help


Dim sNames
Dim sCell
Dim sAlternate
Dim sAcell
Dim sLead
Dim sLcell
Dim sData
Dim sDcell

Sub TestProcess()
sNames = Array("Ron", "Noe", "Adam", "Robert", "Brett", "Abe", "Jon", _
"Christina", "Kevin A.", "Steve U.", "Steven J", "Joe", _
"Jack", "Patick", "Frank")

sCell = Array("20", "32", "30", "23", "35", "22", "38", "17", "37", _
"36", "39", "26", "31", "29", "40")

sAlternate = Array("Steve W.", "Drew", "Ren", "Andrew", "Jerry", _
"Billy", "Kevin D.", "Chase", "Bryce", "Amy")

sAcell = Array("28", "24", "16", "25", "21", "27", "17", "34", "33",
"19")


sLead = Array("Rodger", "Stacy", "Erik")

sLcell = Array("13", "14", "15")

sData = Array("Shane", "Juan", "Phillip", "Noe", "Anthony")

sDcell = Array("42", "43", "44", "45", "46")

ProcessRange ProcessRow:="M", _
DayValue:="Monday", _
TargetColumn:="AB"

ProcessRange ProcessRow:="N10", _
DayValue:="Tuesday", _
TargetColumn:="AC"
End Sub

Sub ProcessRange(ProcessRow As String, _
DayValue As String, _
TargetColumn As String)

If Cells(10, ProcessRow).Value = "15" Then
Cells(90, ProcessRow).ClearContents
Cells(95, ProcessRow).Resize(8).ClearContents
With Cells(11, ProcessRow).Resize(79)
.ClearContents
With .Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideHorizontal).LineStyle = xlNone
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Cells(11, ProcessRow) 'N11
.Font.Bold = True
.Value = DayValue 'Tuesday
End With

With Cells(83, ProcessRow)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With

Cells(84, ProcessRow).Interior.ColorIndex = 6

If Range(TargetColumn & sCell(0)).Value = "N" Then 'AC
Cells(12, ProcessRow).Value = sNames(0)
Else
Cells(12, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(1)).Value = "N" Then
Cells(17, ProcessRow).Value = sNames(1)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(17, ProcessRow) = sAlternate(2)
Cells(17, ProcessRow).Interior.ColorIndex = 45
Else
Cells(17, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(2)).Value = "N" Then
Cells(23, ProcessRow).Value = sNames(2)
Else
Cells(23, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(3)).Value = "N" Then
Cells(27, ProcessRow).Value = sNames(3)
Else
Cells(27, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(4)).Value = "N" Then
Cells(31, ProcessRow).Value = sNames(4)
Else
Cells(31, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(5)).Value = "N" Then
Cells(36, ProcessRow).Value = sNames(5)
ElseIf Range(TargetColumn & sAcell(0)).Value = "N" Then
Cells(36, ProcessRow) = sAlternate(0)
Cells(36, ProcessRow).Interior.ColorIndex = 55
Else
Cells(36, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(6)).Value = "N" Then
Cells(41, ProcessRow).Value = sNames(6)
Else
Cells(41, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(7)).Value = "N" Then
Cells(46, ProcessRow).Value = sNames(7)
Else
Cells(46, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(8)).Value = "N" Then
Cells(51, ProcessRow).Value = sNames(8)
Else
Cells(51, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(9)).Value = "N" Then
Cells(58, ProcessRow).Value = sNames(9)
Else
Cells(58, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(10)).Value = "N" Then
Cells(64, ProcessRow).Value = sNames(10)
Else
Cells(64, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(11)).Value = "N" Then
Cells(69, ProcessRow).Value = sNames(11)
Else
Cells(69, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(12)).Value = "N" Then
Cells(73, ProcessRow).Value = sNames(12)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(73, ProcessRow) = sAlternate(2)
Cells(73, ProcessRow).Interior.ColorIndex = 14
Else
Cells(73, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(13)).Value = "N" Then
Cells(78, ProcessRow).Value = sNames(13)
ElseIf Range(TargetColumn & sAcell(4)).Value = "N" Then
Cells(78, ProcessRow) = sAlternate(4)
Cells(78, ProcessRow).Interior.ColorIndex = 10
Else
Cells(78, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(14)).Value = "N" Then
Cells(84, ProcessRow).Value = sNames(14)
Else
Cells(84, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sLcell(2)).Value = "N" Then
Cells(4, ProcessRow) = sLead(2)
ElseIf Range(TargetColumn & sLcell(0)).Value = "N" Then
Cells(94, ProcessRow) = sLead(0)
ElseIf Range(TargetColumn & sLcell(1)).Value = "N" Then
Cells(94, ProcessRow) = sLead(1)
End If

If Range(TargetColumn & sLcell(0)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(0) Then
Cells(95, ProcessRow) = sLead(0)
End If

If Range(TargetColumn & sLcell(1)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(1) Then
Cells(96, ProcessRow) = sLead(1)
End If
End If

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

erikkeith via OfficeKB.com said:
How can I use this continually referring to a cell within that column? I
have a macro set up to say, "If M:10 equals 12, 13, 13.2, 14 or 15 to run a
sub macro. I have this set up for columns M, N, O, P, Q, V and W so it is
big program (17,000+ lines of code). I am trying to condense it so what I
want to do is right a sub macro that does not have to refer to a specific
column each time. I would rather set up an If then statement to say
something like,
If M:10 equals 12 then
Market9_macro

In this Market9_macro is where I want to be able to not have a column
specified so I can use the same output regardless of what column I chose. i.
e. I want to be able to take my written code for columns M and N and condense
it into one using this theory. To clear this up I will copy the first 2
columns I have:

Dim sNames
sNames = Array("Ron", "Noe", "Adam", "Robert", "Brett", "Abe", "Jon",
"Christina", "Kevin A.", "Steve U.", "Steven J", "Joe", "Jack", "Patick",
"Frank")
Dim sCell
sCell = Array("20", "32", "30", "23", "35", "22", "38", "17", "37", "36",
"39", "26", "31", "29", "40")
Dim sAlternate
sAlternate = Array("Steve W.", "Drew", "Ren", "Andrew", "Jerry", "Billy",
"Kevin D.", "Chase", "Bryce", "Amy")
Dim sAcell
sAcell = Array("28", "24", "16", "25", "21", "27", "17", "34", "33", "19")

Dim sLead
sLead = Array("Rodger", "Stacy", "Erik")
Dim sLcell
sLcell = Array("13", "14", "15")
Dim sData
sData = Array("Shane", "Juan", "Phillip", "Noe", "Anthony")
Dim sDcell
sDcell = Array("42", "43", "44", "45", "46")
Range("M10").Select
If ActiveCell = "15" Then
Range("M90,M95:M102").Select
Selection.ClearContents
Range("M11:M89").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("M11:M89").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("M11").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Monday"
Range("M11,M16,M22,M26,M30,M35,M40,M45,M50,M57,M63,M68,M72,M77,M83").
Select
Range("M83").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("M12,M17,M23,M27,M31,M36,M41,M46,M51,M58,M64,M69,M73,M78,M84").
Select
Range("M84").Activate
Selection.Interior.ColorIndex = 6
Range("M12").Select
If Range("AB" & sCell(0)).Value = "N" Then
ActiveCell.Value = sNames(0)
Else: ActiveCell = "Alternate"
End If
Range("M17").Select
If Range("AB" & sCell(1)).Value = "N" Then
ActiveCell.Value = sNames(1)
Else: ActiveCell = "Alternate"
End If
Range("M23").Select
If Range("AB" & sCell(2)).Value = "N" Then
ActiveCell.Value = sNames(2)
Else: ActiveCell = "Alternate"
End If
Range("M27").Select
If Range("AB" & sCell(3)).Value = "N" Then
ActiveCell.Value = sNames(3)
Else: ActiveCell = "Alternate"
End If
Range("M31").Select
If Range("AB" & sCell(4)).Value = "N" Then
ActiveCell.Value = sNames(4)
Else: ActiveCell = "Alternate"
End If
Range("M36").Select
If Range("AB" & sCell(5)).Value = "N" Then
ActiveCell.Value = sNames(5)
Else: ActiveCell = "Alternate"
End If
Range("M41").Select
If Range("AB" & sCell(6)).Value = "N" Then
ActiveCell.Value = sNames(6)
Else: ActiveCell = "Alternate"
End If
Range("M46").Select
If Range("AB" & sCell(7)).Value = "N" Then
ActiveCell.Value = sNames(7)
Else: ActiveCell = "Alternate"
End If
Range("M51").Select
If Range("AB" & sCell(8)).Value = "N" Then
ActiveCell.Value = sNames(8)
Else: ActiveCell = "Alternate"
End If
Range("M58").Select
If Range("AB" & sCell(9)).Value = "N" Then
ActiveCell.Value = sNames(9)
Else: ActiveCell = "Alternate"
End If
Range("M64").Select
If Range("AB" & sCell(10)).Value = "N" Then
ActiveCell.Value = sNames(10)
Else: ActiveCell = "Alternate"
End If
Range("M69").Select
If Range("AB" & sCell(11)).Value = "N" Then
ActiveCell.Value = sNames(11)
Else: ActiveCell = "Alternate"
End If
Range("M73").Select
If Range("AB" & sCell(12)).Value = "N" Then
ActiveCell.Value = sNames(12)
Else: ActiveCell = "Alternate"
End If
Range("M78").Select
If Range("AB" & sCell(13)).Value = "N" Then
ActiveCell.Value = sNames(13)
ElseIf Range("AB" & sAcell(4)).Value = "N" Then
ActiveCell = sAlternate(4)
Selection.Interior.ColorIndex = 10
Else: ActiveCell = "Alternate"
End If
Range("M84").Select
If Range("AB" & sCell(14)).Value = "N" Then
ActiveCell.Value = sNames(14)
Else: ActiveCell = "Alternate"
End If
Range("M94").Select
If Range("AB" & sLcell(2)).Value = "N" Then
ActiveCell = sLead(2)
ElseIf Range("AB" & sLcell(1)).Value = "N" Then
ActiveCell = sLead(1)
ElseIf Range("AB" & sLcell(0)).Value = "N" Then
ActiveCell = sLead(0)
End If
Range("M95").Select
If Range("AB" & sLcell(1)).Value = "N" And Range("M94").Value <>
sLead(1) Then
ActiveCell = sLead(1)
End If
Range("M96").Select
If Range("AB" & sLcell(0)).Value = "N" And Range("M94").Value <>
sLead(0) Then
ActiveCell = sLead(0)
End If
End If

Range("N10").Select
If ActiveCell = "15" Then
Range("N90,N95:N102").Select
Selection.ClearContents
Range("N11:N89").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("N11:N89").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("N11").Select
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "Tuesday"
Range("N11,N16,N22,N26,N30,N35,N40,N45,N50,N57,N63,N68,N72,N77,N83").
Select
Range("N83").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("N12,N17,N23,N27,N31,N36,N41,N46,N51,N58,N64,N69,N73,N78,N84").
Select
Range("N84").Activate
Selection.Interior.ColorIndex = 6
Range("N12").Select
If Range("AC" & sCell(0)).Value = "N" Then
ActiveCell.Value = sNames(0)
Else: ActiveCell = "Alternate"
End If
Range("N17").Select
If Range("AC" & sCell(1)).Value = "N" Then
ActiveCell.Value = sNames(1)
ElseIf Range("AC" & sAcell(2)).Value = "N" Then
ActiveCell = sAlternate(2)
Selection.Interior.ColorIndex = 45
Else: ActiveCell = "Alternate"
End If
Range("N23").Select
If Range("AC" & sCell(2)).Value = "N" Then
ActiveCell.Value = sNames(2)
Else: ActiveCell = "Alternate"
End If
Range("N27").Select
If Range("AC" & sCell(3)).Value = "N" Then
ActiveCell.Value = sNames(3)
Else: ActiveCell = "Alternate"
End If
Range("N31").Select
If Range("AC" & sCell(4)).Value = "N" Then
ActiveCell.Value = sNames(4)
Else: ActiveCell = "Alternate"
End If
Range("N36").Select
If Range("AC" & sCell(5)).Value = "N" Then
ActiveCell.Value = sNames(5)
ElseIf Range("AC" & sAcell(0)).Value = "N" Then
ActiveCell = sAlternate(0)
Selection.Interior.ColorIndex = 55
Else: ActiveCell = "Alternate"
End If
Range("N41").Select
If Range("AC" & sCell(6)).Value = "N" Then
ActiveCell.Value = sNames(6)
Else: ActiveCell = "Alternate"
End If
Range("N46").Select
If Range("AC" & sCell(7)).Value = "N" Then
ActiveCell.Value = sNames(7)
Else: ActiveCell = "Alternate"
End If
Range("N51").Select
If Range("AC" & sCell(8)).Value = "N" Then
ActiveCell.Value = sNames(8)
Else: ActiveCell = "Alternate"
End If
Range("N58").Select
If Range("AC" & sCell(9)).Value = "N" Then
ActiveCell.Value = sNames(9)
Else: ActiveCell = "Alternate"
End If
Range("N64").Select
If Range("AC" & sCell(10)).Value = "N" Then
ActiveCell.Value = sNames(10)
Else: ActiveCell = "Alternate"
End If
Range("N69").Select
If Range("AC" & sCell(11)).Value = "N" Then
ActiveCell.Value = sNames(11)
Else: ActiveCell = "Alternate"
End If
Range("N73").Select
If Range("AC" & sCell(12)).Value = "N" Then
ActiveCell.Value = sNames(12)
ElseIf Range("AC" & sAcell(2)).Value = "N" Then
ActiveCell = sAlternate(2)
Selection.Interior.ColorIndex = 14
Else: ActiveCell = "Alternate"
End If
Range("N78").Select
If Range("AC" & sCell(13)).Value = "N" Then
ActiveCell.Value = sNames(13)
ElseIf Range("AC" & sAcell(4)).Value = "N" Then
ActiveCell = sAlternate(4)
Selection.Interior.ColorIndex = 10
Else: ActiveCell = "Alternate"
End If
Range("N84").Select
If Range("AC" & sCell(14)).Value = "N" Then
ActiveCell.Value = sNames(14)
Else: ActiveCell = "Alternate"
End If
Range("N94").Select
If Range("AC" & sLcell(2)).Value = "N" Then
ActiveCell = sLead(2)
ElseIf Range("AC" & sLcell(0)).Value = "N" Then
ActiveCell = sLead(0)
ElseIf Range("AC" & sLcell(1)).Value = "N" Then
ActiveCell = sLead(1)
End If
Range("N95").Select
If Range("AC" & sLcell(0)).Value = "N" And Range("N94").Value <>
sLead(0) Then
ActiveCell = sLead(0)
End If
Range("N96").Select
If Range("AC" & sLcell(1)).Value = "N" And Range("N94").Value <>
sLead(1) Then
ActiveCell = sLead(1)
End If
End If

Can you help? Or do I need more specifics?

Bob said:
You could do this, although it hardly seems simpler

With Columns(13)
Union(.Cells(12), .Cells(17), .Cells(23), .Cells(27), _
.Cells(31), .Cells(36), .Cells(41), .Cells(46), _
.Cells(51), .Cells(58), .Cells(64), .Cells(69), _
.Cells(73), .Cells(78), .Cells(84)).Select
End With

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Is there a way to manipulate a column without having to refer to it every
time? For instance, can I somehow select the column by using M:M and then
reference the cell rows within that column of M?

Here is what I have:
Click to expand...

Range("M12,M17,M23,M27,M31,M36,M41,M46,M51,M58,M64,M69,M73,M78,M84").Select
Selection.Interior.ColorIndex = 6
Range("M12").Select
Click to expand...
[quoted text clipped - 16 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
Click to expand...
 
E

erikkeith via OfficeKB.com

Where do I put all the Dim sets? I noticed you started the Sub after them....


Bob said:
I am not really sure what you are asking, but perhaps this will help

Dim sNames
Dim sCell
Dim sAlternate
Dim sAcell
Dim sLead
Dim sLcell
Dim sData
Dim sDcell

Sub TestProcess()
sNames = Array("Ron", "Noe", "Adam", "Robert", "Brett", "Abe", "Jon", _
"Christina", "Kevin A.", "Steve U.", "Steven J", "Joe", _
"Jack", "Patick", "Frank")

sCell = Array("20", "32", "30", "23", "35", "22", "38", "17", "37", _
"36", "39", "26", "31", "29", "40")

sAlternate = Array("Steve W.", "Drew", "Ren", "Andrew", "Jerry", _
"Billy", "Kevin D.", "Chase", "Bryce", "Amy")

sAcell = Array("28", "24", "16", "25", "21", "27", "17", "34", "33",
"19")

sLead = Array("Rodger", "Stacy", "Erik")

sLcell = Array("13", "14", "15")

sData = Array("Shane", "Juan", "Phillip", "Noe", "Anthony")

sDcell = Array("42", "43", "44", "45", "46")

ProcessRange ProcessRow:="M", _
DayValue:="Monday", _
TargetColumn:="AB"

ProcessRange ProcessRow:="N10", _
DayValue:="Tuesday", _
TargetColumn:="AC"
End Sub

Sub ProcessRange(ProcessRow As String, _
DayValue As String, _
TargetColumn As String)

If Cells(10, ProcessRow).Value = "15" Then
Cells(90, ProcessRow).ClearContents
Cells(95, ProcessRow).Resize(8).ClearContents
With Cells(11, ProcessRow).Resize(79)
.ClearContents
With .Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideHorizontal).LineStyle = xlNone
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Cells(11, ProcessRow) 'N11
.Font.Bold = True
.Value = DayValue 'Tuesday
End With

With Cells(83, ProcessRow)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With

Cells(84, ProcessRow).Interior.ColorIndex = 6

If Range(TargetColumn & sCell(0)).Value = "N" Then 'AC
Cells(12, ProcessRow).Value = sNames(0)
Else
Cells(12, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(1)).Value = "N" Then
Cells(17, ProcessRow).Value = sNames(1)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(17, ProcessRow) = sAlternate(2)
Cells(17, ProcessRow).Interior.ColorIndex = 45
Else
Cells(17, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(2)).Value = "N" Then
Cells(23, ProcessRow).Value = sNames(2)
Else
Cells(23, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(3)).Value = "N" Then
Cells(27, ProcessRow).Value = sNames(3)
Else
Cells(27, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(4)).Value = "N" Then
Cells(31, ProcessRow).Value = sNames(4)
Else
Cells(31, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(5)).Value = "N" Then
Cells(36, ProcessRow).Value = sNames(5)
ElseIf Range(TargetColumn & sAcell(0)).Value = "N" Then
Cells(36, ProcessRow) = sAlternate(0)
Cells(36, ProcessRow).Interior.ColorIndex = 55
Else
Cells(36, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(6)).Value = "N" Then
Cells(41, ProcessRow).Value = sNames(6)
Else
Cells(41, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(7)).Value = "N" Then
Cells(46, ProcessRow).Value = sNames(7)
Else
Cells(46, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(8)).Value = "N" Then
Cells(51, ProcessRow).Value = sNames(8)
Else
Cells(51, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(9)).Value = "N" Then
Cells(58, ProcessRow).Value = sNames(9)
Else
Cells(58, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(10)).Value = "N" Then
Cells(64, ProcessRow).Value = sNames(10)
Else
Cells(64, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(11)).Value = "N" Then
Cells(69, ProcessRow).Value = sNames(11)
Else
Cells(69, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(12)).Value = "N" Then
Cells(73, ProcessRow).Value = sNames(12)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(73, ProcessRow) = sAlternate(2)
Cells(73, ProcessRow).Interior.ColorIndex = 14
Else
Cells(73, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(13)).Value = "N" Then
Cells(78, ProcessRow).Value = sNames(13)
ElseIf Range(TargetColumn & sAcell(4)).Value = "N" Then
Cells(78, ProcessRow) = sAlternate(4)
Cells(78, ProcessRow).Interior.ColorIndex = 10
Else
Cells(78, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(14)).Value = "N" Then
Cells(84, ProcessRow).Value = sNames(14)
Else
Cells(84, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sLcell(2)).Value = "N" Then
Cells(4, ProcessRow) = sLead(2)
ElseIf Range(TargetColumn & sLcell(0)).Value = "N" Then
Cells(94, ProcessRow) = sLead(0)
ElseIf Range(TargetColumn & sLcell(1)).Value = "N" Then
Cells(94, ProcessRow) = sLead(1)
End If

If Range(TargetColumn & sLcell(0)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(0) Then
Cells(95, ProcessRow) = sLead(0)
End If

If Range(TargetColumn & sLcell(1)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(1) Then
Cells(96, ProcessRow) = sLead(1)
End If
End If

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
How can I use this continually referring to a cell within that column? I
have a macro set up to say, "If M:10 equals 12, 13, 13.2, 14 or 15 to run a
Click to expand...
[quoted text clipped - 433 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
 
B

Bob Phillips

Before any macros, then they are in scope of all macros in that module.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

erikkeith via OfficeKB.com said:
Where do I put all the Dim sets? I noticed you started the Sub after them....


Bob said:
I am not really sure what you are asking, but perhaps this will help

Dim sNames
Dim sCell
Dim sAlternate
Dim sAcell
Dim sLead
Dim sLcell
Dim sData
Dim sDcell

Sub TestProcess()
sNames = Array("Ron", "Noe", "Adam", "Robert", "Brett", "Abe", "Jon", _
"Christina", "Kevin A.", "Steve U.", "Steven J", "Joe", _
"Jack", "Patick", "Frank")

sCell = Array("20", "32", "30", "23", "35", "22", "38", "17", "37", _
"36", "39", "26", "31", "29", "40")

sAlternate = Array("Steve W.", "Drew", "Ren", "Andrew", "Jerry", _
"Billy", "Kevin D.", "Chase", "Bryce", "Amy")

sAcell = Array("28", "24", "16", "25", "21", "27", "17", "34", "33",
"19")

sLead = Array("Rodger", "Stacy", "Erik")

sLcell = Array("13", "14", "15")

sData = Array("Shane", "Juan", "Phillip", "Noe", "Anthony")

sDcell = Array("42", "43", "44", "45", "46")

ProcessRange ProcessRow:="M", _
DayValue:="Monday", _
TargetColumn:="AB"

ProcessRange ProcessRow:="N10", _
DayValue:="Tuesday", _
TargetColumn:="AC"
End Sub

Sub ProcessRange(ProcessRow As String, _
DayValue As String, _
TargetColumn As String)

If Cells(10, ProcessRow).Value = "15" Then
Cells(90, ProcessRow).ClearContents
Cells(95, ProcessRow).Resize(8).ClearContents
With Cells(11, ProcessRow).Resize(79)
.ClearContents
With .Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideHorizontal).LineStyle = xlNone
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Cells(11, ProcessRow) 'N11
.Font.Bold = True
.Value = DayValue 'Tuesday
End With

With Cells(83, ProcessRow)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With

Cells(84, ProcessRow).Interior.ColorIndex = 6

If Range(TargetColumn & sCell(0)).Value = "N" Then 'AC
Cells(12, ProcessRow).Value = sNames(0)
Else
Cells(12, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(1)).Value = "N" Then
Cells(17, ProcessRow).Value = sNames(1)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(17, ProcessRow) = sAlternate(2)
Cells(17, ProcessRow).Interior.ColorIndex = 45
Else
Cells(17, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(2)).Value = "N" Then
Cells(23, ProcessRow).Value = sNames(2)
Else
Cells(23, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(3)).Value = "N" Then
Cells(27, ProcessRow).Value = sNames(3)
Else
Cells(27, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(4)).Value = "N" Then
Cells(31, ProcessRow).Value = sNames(4)
Else
Cells(31, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(5)).Value = "N" Then
Cells(36, ProcessRow).Value = sNames(5)
ElseIf Range(TargetColumn & sAcell(0)).Value = "N" Then
Cells(36, ProcessRow) = sAlternate(0)
Cells(36, ProcessRow).Interior.ColorIndex = 55
Else
Cells(36, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(6)).Value = "N" Then
Cells(41, ProcessRow).Value = sNames(6)
Else
Cells(41, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(7)).Value = "N" Then
Cells(46, ProcessRow).Value = sNames(7)
Else
Cells(46, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(8)).Value = "N" Then
Cells(51, ProcessRow).Value = sNames(8)
Else
Cells(51, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(9)).Value = "N" Then
Cells(58, ProcessRow).Value = sNames(9)
Else
Cells(58, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(10)).Value = "N" Then
Cells(64, ProcessRow).Value = sNames(10)
Else
Cells(64, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(11)).Value = "N" Then
Cells(69, ProcessRow).Value = sNames(11)
Else
Cells(69, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(12)).Value = "N" Then
Cells(73, ProcessRow).Value = sNames(12)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(73, ProcessRow) = sAlternate(2)
Cells(73, ProcessRow).Interior.ColorIndex = 14
Else
Cells(73, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(13)).Value = "N" Then
Cells(78, ProcessRow).Value = sNames(13)
ElseIf Range(TargetColumn & sAcell(4)).Value = "N" Then
Cells(78, ProcessRow) = sAlternate(4)
Cells(78, ProcessRow).Interior.ColorIndex = 10
Else
Cells(78, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(14)).Value = "N" Then
Cells(84, ProcessRow).Value = sNames(14)
Else
Cells(84, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sLcell(2)).Value = "N" Then
Cells(4, ProcessRow) = sLead(2)
ElseIf Range(TargetColumn & sLcell(0)).Value = "N" Then
Cells(94, ProcessRow) = sLead(0)
ElseIf Range(TargetColumn & sLcell(1)).Value = "N" Then
Cells(94, ProcessRow) = sLead(1)
End If

If Range(TargetColumn & sLcell(0)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(0) Then
Cells(95, ProcessRow) = sLead(0)
End If

If Range(TargetColumn & sLcell(1)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(1) Then
Cells(96, ProcessRow) = sLead(1)
End If
End If

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
How can I use this continually referring to a cell within that column? I
have a macro set up to say, "If M:10 equals 12, 13, 13.2, 14 or 15 to
Click to expand...
run a
[quoted text clipped - 433 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
Click to expand...
 
E

erikkeith via OfficeKB.com

How can I string Cells together when running this syntax?

Cells(84, ProcessRow)

I need to process additional cells. I tried this but it did not work:

Cells(17, 18, 19, 84, ProcessRow)

Bob said:
I am not really sure what you are asking, but perhaps this will help

Dim sNames
Dim sCell
Dim sAlternate
Dim sAcell
Dim sLead
Dim sLcell
Dim sData
Dim sDcell

Sub TestProcess()
sNames = Array("Ron", "Noe", "Adam", "Robert", "Brett", "Abe", "Jon", _
"Christina", "Kevin A.", "Steve U.", "Steven J", "Joe", _
"Jack", "Patick", "Frank")

sCell = Array("20", "32", "30", "23", "35", "22", "38", "17", "37", _
"36", "39", "26", "31", "29", "40")

sAlternate = Array("Steve W.", "Drew", "Ren", "Andrew", "Jerry", _
"Billy", "Kevin D.", "Chase", "Bryce", "Amy")

sAcell = Array("28", "24", "16", "25", "21", "27", "17", "34", "33",
"19")

sLead = Array("Rodger", "Stacy", "Erik")

sLcell = Array("13", "14", "15")

sData = Array("Shane", "Juan", "Phillip", "Noe", "Anthony")

sDcell = Array("42", "43", "44", "45", "46")

ProcessRange ProcessRow:="M", _
DayValue:="Monday", _
TargetColumn:="AB"

ProcessRange ProcessRow:="N10", _
DayValue:="Tuesday", _
TargetColumn:="AC"
End Sub

Sub ProcessRange(ProcessRow As String, _
DayValue As String, _
TargetColumn As String)

If Cells(10, ProcessRow).Value = "15" Then
Cells(90, ProcessRow).ClearContents
Cells(95, ProcessRow).Resize(8).ClearContents
With Cells(11, ProcessRow).Resize(79)
.ClearContents
With .Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideHorizontal).LineStyle = xlNone
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Cells(11, ProcessRow) 'N11
.Font.Bold = True
.Value = DayValue 'Tuesday
End With

With Cells(83, ProcessRow)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With

Cells(84, ProcessRow).Interior.ColorIndex = 6

If Range(TargetColumn & sCell(0)).Value = "N" Then 'AC
Cells(12, ProcessRow).Value = sNames(0)
Else
Cells(12, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(1)).Value = "N" Then
Cells(17, ProcessRow).Value = sNames(1)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(17, ProcessRow) = sAlternate(2)
Cells(17, ProcessRow).Interior.ColorIndex = 45
Else
Cells(17, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(2)).Value = "N" Then
Cells(23, ProcessRow).Value = sNames(2)
Else
Cells(23, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(3)).Value = "N" Then
Cells(27, ProcessRow).Value = sNames(3)
Else
Cells(27, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(4)).Value = "N" Then
Cells(31, ProcessRow).Value = sNames(4)
Else
Cells(31, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(5)).Value = "N" Then
Cells(36, ProcessRow).Value = sNames(5)
ElseIf Range(TargetColumn & sAcell(0)).Value = "N" Then
Cells(36, ProcessRow) = sAlternate(0)
Cells(36, ProcessRow).Interior.ColorIndex = 55
Else
Cells(36, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(6)).Value = "N" Then
Cells(41, ProcessRow).Value = sNames(6)
Else
Cells(41, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(7)).Value = "N" Then
Cells(46, ProcessRow).Value = sNames(7)
Else
Cells(46, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(8)).Value = "N" Then
Cells(51, ProcessRow).Value = sNames(8)
Else
Cells(51, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(9)).Value = "N" Then
Cells(58, ProcessRow).Value = sNames(9)
Else
Cells(58, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(10)).Value = "N" Then
Cells(64, ProcessRow).Value = sNames(10)
Else
Cells(64, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(11)).Value = "N" Then
Cells(69, ProcessRow).Value = sNames(11)
Else
Cells(69, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(12)).Value = "N" Then
Cells(73, ProcessRow).Value = sNames(12)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(73, ProcessRow) = sAlternate(2)
Cells(73, ProcessRow).Interior.ColorIndex = 14
Else
Cells(73, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(13)).Value = "N" Then
Cells(78, ProcessRow).Value = sNames(13)
ElseIf Range(TargetColumn & sAcell(4)).Value = "N" Then
Cells(78, ProcessRow) = sAlternate(4)
Cells(78, ProcessRow).Interior.ColorIndex = 10
Else
Cells(78, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(14)).Value = "N" Then
Cells(84, ProcessRow).Value = sNames(14)
Else
Cells(84, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sLcell(2)).Value = "N" Then
Cells(4, ProcessRow) = sLead(2)
ElseIf Range(TargetColumn & sLcell(0)).Value = "N" Then
Cells(94, ProcessRow) = sLead(0)
ElseIf Range(TargetColumn & sLcell(1)).Value = "N" Then
Cells(94, ProcessRow) = sLead(1)
End If

If Range(TargetColumn & sLcell(0)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(0) Then
Cells(95, ProcessRow) = sLead(0)
End If

If Range(TargetColumn & sLcell(1)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(1) Then
Cells(96, ProcessRow) = sLead(1)
End If
End If

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
How can I use this continually referring to a cell within that column? I
have a macro set up to say, "If M:10 equals 12, 13, 13.2, 14 or 15 to run a
Click to expand...
[quoted text clipped - 433 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
 
B

Bob Phillips

No test them each

If Cells(17, ProcessRow).Value = "value1" And Cells(18, ProcessRow).Value =
"value2" And ...


If Cells(17, ProcessRow).Value = "value1" Or Cells(18, ProcessRow).Value =
"value2" Or ...


depending upon what you are doing


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

erikkeith via OfficeKB.com said:
How can I string Cells together when running this syntax?

Cells(84, ProcessRow)

I need to process additional cells. I tried this but it did not work:

Cells(17, 18, 19, 84, ProcessRow)

Bob said:
I am not really sure what you are asking, but perhaps this will help

Dim sNames
Dim sCell
Dim sAlternate
Dim sAcell
Dim sLead
Dim sLcell
Dim sData
Dim sDcell

Sub TestProcess()
sNames = Array("Ron", "Noe", "Adam", "Robert", "Brett", "Abe", "Jon", _
"Christina", "Kevin A.", "Steve U.", "Steven J", "Joe", _
"Jack", "Patick", "Frank")

sCell = Array("20", "32", "30", "23", "35", "22", "38", "17", "37", _
"36", "39", "26", "31", "29", "40")

sAlternate = Array("Steve W.", "Drew", "Ren", "Andrew", "Jerry", _
"Billy", "Kevin D.", "Chase", "Bryce", "Amy")

sAcell = Array("28", "24", "16", "25", "21", "27", "17", "34", "33",
"19")

sLead = Array("Rodger", "Stacy", "Erik")

sLcell = Array("13", "14", "15")

sData = Array("Shane", "Juan", "Phillip", "Noe", "Anthony")

sDcell = Array("42", "43", "44", "45", "46")

ProcessRange ProcessRow:="M", _
DayValue:="Monday", _
TargetColumn:="AB"

ProcessRange ProcessRow:="N10", _
DayValue:="Tuesday", _
TargetColumn:="AC"
End Sub

Sub ProcessRange(ProcessRow As String, _
DayValue As String, _
TargetColumn As String)

If Cells(10, ProcessRow).Value = "15" Then
Cells(90, ProcessRow).ClearContents
Cells(95, ProcessRow).Resize(8).ClearContents
With Cells(11, ProcessRow).Resize(79)
.ClearContents
With .Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideHorizontal).LineStyle = xlNone
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Cells(11, ProcessRow) 'N11
.Font.Bold = True
.Value = DayValue 'Tuesday
End With

With Cells(83, ProcessRow)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With

Cells(84, ProcessRow).Interior.ColorIndex = 6

If Range(TargetColumn & sCell(0)).Value = "N" Then 'AC
Cells(12, ProcessRow).Value = sNames(0)
Else
Cells(12, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(1)).Value = "N" Then
Cells(17, ProcessRow).Value = sNames(1)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(17, ProcessRow) = sAlternate(2)
Cells(17, ProcessRow).Interior.ColorIndex = 45
Else
Cells(17, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(2)).Value = "N" Then
Cells(23, ProcessRow).Value = sNames(2)
Else
Cells(23, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(3)).Value = "N" Then
Cells(27, ProcessRow).Value = sNames(3)
Else
Cells(27, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(4)).Value = "N" Then
Cells(31, ProcessRow).Value = sNames(4)
Else
Cells(31, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(5)).Value = "N" Then
Cells(36, ProcessRow).Value = sNames(5)
ElseIf Range(TargetColumn & sAcell(0)).Value = "N" Then
Cells(36, ProcessRow) = sAlternate(0)
Cells(36, ProcessRow).Interior.ColorIndex = 55
Else
Cells(36, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(6)).Value = "N" Then
Cells(41, ProcessRow).Value = sNames(6)
Else
Cells(41, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(7)).Value = "N" Then
Cells(46, ProcessRow).Value = sNames(7)
Else
Cells(46, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(8)).Value = "N" Then
Cells(51, ProcessRow).Value = sNames(8)
Else
Cells(51, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(9)).Value = "N" Then
Cells(58, ProcessRow).Value = sNames(9)
Else
Cells(58, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(10)).Value = "N" Then
Cells(64, ProcessRow).Value = sNames(10)
Else
Cells(64, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(11)).Value = "N" Then
Cells(69, ProcessRow).Value = sNames(11)
Else
Cells(69, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(12)).Value = "N" Then
Cells(73, ProcessRow).Value = sNames(12)
ElseIf Range(TargetColumn & sAcell(2)).Value = "N" Then
Cells(73, ProcessRow) = sAlternate(2)
Cells(73, ProcessRow).Interior.ColorIndex = 14
Else
Cells(73, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(13)).Value = "N" Then
Cells(78, ProcessRow).Value = sNames(13)
ElseIf Range(TargetColumn & sAcell(4)).Value = "N" Then
Cells(78, ProcessRow) = sAlternate(4)
Cells(78, ProcessRow).Interior.ColorIndex = 10
Else
Cells(78, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sCell(14)).Value = "N" Then
Cells(84, ProcessRow).Value = sNames(14)
Else
Cells(84, ProcessRow) = "Alternate"
End If

If Range(TargetColumn & sLcell(2)).Value = "N" Then
Cells(4, ProcessRow) = sLead(2)
ElseIf Range(TargetColumn & sLcell(0)).Value = "N" Then
Cells(94, ProcessRow) = sLead(0)
ElseIf Range(TargetColumn & sLcell(1)).Value = "N" Then
Cells(94, ProcessRow) = sLead(1)
End If

If Range(TargetColumn & sLcell(0)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(0) Then
Cells(95, ProcessRow) = sLead(0)
End If

If Range(TargetColumn & sLcell(1)).Value = "N" And _
Cells(94, ProcessRow).Value <> sLead(1) Then
Cells(96, ProcessRow) = sLead(1)
End If
End If

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
How can I use this continually referring to a cell within that column? I
have a macro set up to say, "If M:10 equals 12, 13, 13.2, 14 or 15 to
Click to expand...
run a
[quoted text clipped - 433 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
Click to expand...
 
E

erikkeith via OfficeKB.com

Actually, I am looking for a selection of cells to be a specific color so I
need something that can string them (something like):

Cells(17, 18, 19, 84, ProcessRow).Interior.ColorIndex = 6

Bob said:
No test them each

If Cells(17, ProcessRow).Value = "value1" And Cells(18, ProcessRow).Value =
"value2" And ...

If Cells(17, ProcessRow).Value = "value1" Or Cells(18, ProcessRow).Value =
"value2" Or ...

depending upon what you are doing

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
How can I string Cells together when running this syntax?
Click to expand...
[quoted text clipped - 259 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
 
B

Bob Phillips

Test them all as I showed.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

erikkeith via OfficeKB.com said:
Actually, I am looking for a selection of cells to be a specific color so I
need something that can string them (something like):

Cells(17, 18, 19, 84, ProcessRow).Interior.ColorIndex = 6

Bob said:
No test them each

If Cells(17, ProcessRow).Value = "value1" And Cells(18, ProcessRow).Value =
"value2" And ...

If Cells(17, ProcessRow).Value = "value1" Or Cells(18, ProcessRow).Value =
"value2" Or ...

depending upon what you are doing

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
How can I string Cells together when running this syntax?
Click to expand...
[quoted text clipped - 259 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
Click to expand...
 
E

erikkeith via OfficeKB.com

Well I can do this for a color selection but what can I do for the following?:


Range("M12,M17,M23,M27,M31,M36").Select
If Range("AB" & sAcell(0)).Value = "N" And Application.CountIf(Range
("M12:M84"), sAlternate(0)) = 0 Then
On Error Resume Next
Selection.Find(What:="Alternate", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="Alternate", Replacement:=sAlternate(0),
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If ActiveCell = sAlternate(0) Then
ActiveCell.Interior.ColorIndex = 55
End If
End If
If Range("AB" & sAcell(0)).Value = "N" And Application.CountIf(Range
("M12:M84"), sAlternate(0)) = 0 Then
Range("M38").Value = sAlternate(0)
End If

Where I referrence the "M" column and cells how can I write it with the new
code?

Bob said:
Test them all as I showed.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Actually, I am looking for a selection of cells to be a specific color so I
need something that can string them (something like):
Click to expand...
[quoted text clipped - 28 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
 
E

erikkeith via OfficeKB.com

Can anyone help me with this? I am stuck.
Well I can do this for a color selection but what can I do for the following?:

Range("M12,M17,M23,M27,M31,M36").Select
If Range("AB" & sAcell(0)).Value = "N" And Application.CountIf(Range
("M12:M84"), sAlternate(0)) = 0 Then
On Error Resume Next
Selection.Find(What:="Alternate", LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="Alternate", Replacement:=sAlternate(0),
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
If ActiveCell = sAlternate(0) Then
ActiveCell.Interior.ColorIndex = 55
End If
End If
If Range("AB" & sAcell(0)).Value = "N" And Application.CountIf(Range
("M12:M84"), sAlternate(0)) = 0 Then
Range("M38").Value = sAlternate(0)
End If

Where I referrence the "M" column and cells how can I write it with the new
code?
Test them all as I showed.
Click to expand...
[quoted text clipped - 11 lines]
Click to expand...
 
E

erikkeith via OfficeKB.com

First of all Bob let me say thanks! You have helped greatly! I have one
last hurdle to overcome...

How can I make this work?:

Cells(17, ProcessRow).Resize(19).Select & Cells(41, ProcessRow).Resize(43).
Select

I am trying to select 2 groups of cells so I can run a search within them.
Can this be done?

Bob said:
Test them all as I showed.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Actually, I am looking for a selection of cells to be a specific color so I
need something that can string them (something like):
Click to expand...
[quoted text clipped - 28 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
 
B

Bob Phillips

Union(Cells(17, ProcessRow).Resize(19,Cells(41,
ProcessRow).Resize(43)).Select


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

erikkeith via OfficeKB.com said:
First of all Bob let me say thanks! You have helped greatly! I have one
last hurdle to overcome...

How can I make this work?:

Cells(17, ProcessRow).Resize(19).Select & Cells(41, ProcessRow).Resize(43).
Select

I am trying to select 2 groups of cells so I can run a search within them.
Can this be done?

Bob said:
Test them all as I showed.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Actually, I am looking for a selection of cells to be a specific color so I
need something that can string them (something like):
Click to expand...
[quoted text clipped - 28 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200611/1
Click to expand...
Click to expand...
Click to expand...
 
E

erikkeith via OfficeKB.com

Bob, thank you so much for your help. I have now reduced my macro by more
than 11,000 lines because of this. It was 17,700! lol Much thanks.

Bob said:
Union(Cells(17, ProcessRow).Resize(19,Cells(41,
ProcessRow).Resize(43)).Select

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
First of all Bob let me say thanks! You have helped greatly! I have one
last hurdle to overcome...
Click to expand...
[quoted text clipped - 25 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help in VBA 5
HELP!! Macro Deletes Last Interval 4
help with textbox tabkeybehavior 0
Help wanted with Code, Please 6
Nested If/Then Help 2
Help required with a simple Loop 10
Help with Code 3
MACRO HELP, PASTE CELL TO COLUMN 0

Top