PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Basic help

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Basic help

 
 
erikkeith via OfficeKB.com
Guest
Posts: n/a
 
      20th Nov 2006
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1
 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      20th Nov 2006
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)

"erikkeith via OfficeKB.com" <u13156@uwe> wrote in message
news:69908edbdce15@uwe...
> 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
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200611/1
>
 
Reply With Quote
 
erikkeith via OfficeKB.com
Guest
Posts: n/a
 
      22nd Nov 2006
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 Phillips wrote:
>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
>[quoted text clipped - 16 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
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      22nd Nov 2006
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" <u13156@uwe> wrote in message
news:69a8c0e8bbc2e@uwe...
> 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 Phillips wrote:
> >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
> >[quoted text clipped - 16 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
>
 
Reply With Quote
 
erikkeith via OfficeKB.com
Guest
Posts: n/a
 
      22nd Nov 2006
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
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      22nd Nov 2006
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
>
 
Reply With Quote
 
erikkeith via OfficeKB.com
Guest
Posts: n/a
 
      27th Nov 2006
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 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
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      27th Nov 2006
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" <u13156@uwe> wrote in message
news:69e7933e34f5e@uwe...
> 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 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
>
 
Reply With Quote
 
erikkeith via OfficeKB.com
Guest
Posts: n/a
 
      27th Nov 2006
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 Phillips wrote:
>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?
>>
>[quoted text clipped - 259 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
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      27th Nov 2006
Test them all as I showed.

--

HTH

Bob Phillips

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

"erikkeith via OfficeKB.com" <u13156@uwe> wrote in message
news:69e858f5c81ab@uwe...
> 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 Phillips wrote:
> >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?
> >>
> >[quoted text clipped - 259 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
>
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Excel VBA UDF Userdefined function - search in partially empty fie | Question on how to do a COUNTIF type of statement »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Basic way to make basic databases work on Access2007? Fred Microsoft Access 5 8th Apr 2010 04:47 PM
Re: Converting Quick Basic to Visual Basic Mike Williams Microsoft VB .NET 0 25th Sep 2009 09:59 AM
have window vista basic.understand basic don't have fax & scan jay sureka Windows Vista Print / Fax / Scan 4 7th Jan 2008 07:02 PM
Basic Difference between Visual Basic 6 and Microsoft Access VBA =?Utf-8?B?SXJzaGFkIEFsYW0=?= Microsoft Access 2 5th Apr 2007 12:34 PM
i have office basic 2003,does IRM work with basic? =?Utf-8?B?T29sYQ==?= Microsoft Outlook 6 7th Jun 2006 06:09 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 06:19 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top