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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic help
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic help
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic help
Well I can do this for a color selection but what can I do for the following?:
Range("M12,M17,M23,M27,M31,M36").Select If Range("AB" & sAcell(0)).Value = "N" And Application.CountIf(Range ("M12:M84"), sAlternate(0)) = 0 Then On Error Resume Next Selection.Find(What:="Alternate", LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Replace What:="Alternate", Replacement:=sAlternate(0), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False If ActiveCell = sAlternate(0) Then ActiveCell.Interior.ColorIndex = 55 End If End If If Range("AB" & sAcell(0)).Value = "N" And Application.CountIf(Range ("M12:M84"), sAlternate(0)) = 0 Then Range("M38").Value = sAlternate(0) End If Where I referrence the "M" column and cells how can I write it with the new code? Bob Phillips wrote: Test them all as I showed. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) Actually, I am looking for a selection of cells to be a specific color so I need something that can string them (something like): [quoted text clipped - 28 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic help
Can anyone help me with this? I am stuck.
erikkeith wrote: Well I can do this for a color selection but what can I do for the following?: Range("M12,M17,M23,M27,M31,M36").Select If Range("AB" & sAcell(0)).Value = "N" And Application.CountIf(Range ("M12:M84"), sAlternate(0)) = 0 Then On Error Resume Next Selection.Find(What:="Alternate", LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Replace What:="Alternate", Replacement:=sAlternate(0), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False If ActiveCell = sAlternate(0) Then ActiveCell.Interior.ColorIndex = 55 End If End If If Range("AB" & sAcell(0)).Value = "N" And Application.CountIf(Range ("M12:M84"), sAlternate(0)) = 0 Then Range("M38").Value = sAlternate(0) End If Where I referrence the "M" column and cells how can I write it with the new code? Test them all as I showed. [quoted text clipped - 11 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic help
First of all Bob let me say thanks! You have helped greatly! I have one
last hurdle to overcome... How can I make this work?: Cells(17, ProcessRow).Resize(19).Select & Cells(41, ProcessRow).Resize(43). Select I am trying to select 2 groups of cells so I can run a search within them. Can this be done? Bob Phillips wrote: Test them all as I showed. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) Actually, I am looking for a selection of cells to be a specific color so I need something that can string them (something like): [quoted text clipped - 28 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 -- Message posted via http://www.officekb.com |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic help
Union(Cells(17, ProcessRow).Resize(19,Cells(41,
ProcessRow).Resize(43)).Select -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "erikkeith via OfficeKB.com" <u13156@uwe wrote in message news:6a0029bcea910@uwe... First of all Bob let me say thanks! You have helped greatly! I have one last hurdle to overcome... How can I make this work?: Cells(17, ProcessRow).Resize(19).Select & Cells(41, ProcessRow).Resize(43). Select I am trying to select 2 groups of cells so I can run a search within them. Can this be done? Bob Phillips wrote: Test them all as I showed. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) Actually, I am looking for a selection of cells to be a specific color so I need something that can string them (something like): [quoted text clipped - 28 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200611/1 -- Message posted via http://www.officekb.com |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Basic help
Bob, thank you so much for your help. I have now reduced my macro by more
than 11,000 lines because of this. It was 17,700! lol Much thanks. Bob Phillips wrote: Union(Cells(17, ProcessRow).Resize(19,Cells(41, ProcessRow).Resize(43)).Select -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) First of all Bob let me say thanks! You have helped greatly! I have one last hurdle to overcome... [quoted text clipped - 25 lines] -- Message posted via http://www.officekb.com -- Message posted via http://www.officekb.com |
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 |