Basic help
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" <u13156@uwe wrote in message
news:69aa197ce4ddc@uwe...
Where do I put all the Dim sets? I noticed you started the Sub after
them....
Bob Phillips wrote:
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
[quoted text clipped - 433 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1
|