| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic help
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,8 4").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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Basic help
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,8 4").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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Basic help
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,M6 4,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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Basic help
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,M6 4,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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Basic help
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Basic help
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Basic help
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Very basic VBA | Excel Programming | |||
| Can I run Visual Basic procedure using Excel Visual Basic editor? | Excel Programming | |||
| Basic If | Excel Worksheet Functions | |||
| Need some basic help | Excel Programming | |||
| probably 2 basic | Excel Programming | |||
Hybrid Mode
