Thread: What's wrong with this macro?
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Gary Keramidas Gary Keramidas is offline
external usenet poster
  
Posts: 2,494
Default What's wrong with this macro?
if all of the ranges are the same, just on different sheets, i think this is
one way you could shorten the code to make it more manageable


Dim i As Integer
Dim arr As Variant
arr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep",
"Oct", "Nov", "Dec")

For i = LBound(arr) To UBound(arr)
Sheets(arr(i)).Range("a7").Formula = "=INDEX(Summary!A:A,MATCH(" & arr(i) &
"!B7,Summary!B:B,0))"
Sheets(arr(i)).Range("a7").AutoFill
Destination:=Sheets(arr(i)).Range("a7:a121")
Sheets(arr(i)).Range("a7:a121").Value =
Sheets(arr(i)).Range("a7:a121").Value
Sheets(arr(i)).Rows("7:121").Sort Key1:=Sheets(arr(i)).Range("a7")

Next i
End Sub


--


Gary


"belly0fdesire"
wrote in message
news:belly0fdesire.223ve0_1138050005.0906@excelfor um-nospam.com...

Okay. Thank you for the advice. I have included the code below... I
don't know if anyone will be able to help without just downloading the
file. There's nothing to be afraid or worried about. It's a very
impressive spreadsheet that I've designed and I'm pretty proud of it,
but I'm just not very good with VBA code... if anyone could help, it
would be very very appreciated...



Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iClr As Integer

On Error Resume Next

If Target.Count = 1 And (Target.Column = 2 Or Target.Column = 6) Then
Range("A6:O120").Sort Key1:=Range("F6"), Order1:=xlAscending,
Key2:=Range("B6") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

'All months
Sheets("Jan").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jan!B7,Summary!B:B,0 ))"
Sheets("Jan").Range("a7").AutoFill
Destination:=Sheets("Jan").Range("a7:a121")
Sheets("Jan").Range("a7:a121").Value =
Sheets("Jan").Range("a7:a121").Value
Sheets("Jan").Rows("7:121").Sort Key1:=Sheets("Jan").Range("a7")
Sheets("Feb").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Feb!B7,Summary!B:B,0 ))"
Sheets("Feb").Range("a7").AutoFill
Destination:=Sheets("Feb").Range("a7:a121")
Sheets("Feb").Range("a7:a121").Value =
Sheets("Feb").Range("a7:a121").Value
Sheets("Feb").Rows("7:121").Sort Key1:=Sheets("Feb").Range("a7")
Sheets("Mar").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Mar!B7,Summary!B:B,0 ))"
Sheets("Mar").Range("a7").AutoFill
Destination:=Sheets("Mar").Range("a7:a121")
Sheets("Mar").Range("a7:a121").Value =
Sheets("Mar").Range("a7:a121").Value
Sheets("Mar").Rows("7:121").Sort Key1:=Sheets("Mar").Range("a7")
Sheets("Apr").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Apr!B7,Summary!B:B,0 ))"
Sheets("Apr").Range("a7").AutoFill
Destination:=Sheets("Apr").Range("a7:a121")
Sheets("Apr").Range("a7:a121").Value =
Sheets("Apr").Range("a7:a121").Value
Sheets("Apr").Rows("7:121").Sort Key1:=Sheets("Apr").Range("a7")
Sheets("May").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(May!B7,Summary!B:B,0 ))"
Sheets("May").Range("a7").AutoFill
Destination:=Sheets("May").Range("a7:a121")
Sheets("May").Range("a7:a121").Value =
Sheets("May").Range("a7:a121").Value
Sheets("May").Rows("7:121").Sort Key1:=Sheets("May").Range("a7")
Sheets("Jun").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jun!B7,Summary!B:B,0 ))"
Sheets("Jun").Range("a7").AutoFill
Destination:=Sheets("Jun").Range("a7:a121")
Sheets("Jun").Range("a7:a121").Value =
Sheets("Jun").Range("a7:a121").Value
Sheets("Jun").Rows("7:121").Sort Key1:=Sheets("Jun").Range("a7")
Sheets("Jul").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Jul!B7,Summary!B:B,0 ))"
Sheets("Jul").Range("a7").AutoFill
Destination:=Sheets("Jul").Range("a7:a121")
Sheets("Jul").Range("a7:a121").Value =
Sheets("Jul").Range("a7:a121").Value
Sheets("Jul").Rows("7:121").Sort Key1:=Sheets("Jul").Range("a7")
Sheets("Aug").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Aug!B7,Summary!B:B,0 ))"
Sheets("Aug").Range("a7").AutoFill
Destination:=Sheets("Aug").Range("a7:a121")
Sheets("Aug").Range("a7:a121").Value =
Sheets("Aug").Range("a7:a121").Value
Sheets("Aug").Rows("7:121").Sort Key1:=Sheets("Aug").Range("a7")
Sheets("Sep").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Sep!B7,Summary!B:B,0 ))"
Sheets("Sep").Range("a7").AutoFill
Destination:=Sheets("Sep").Range("a7:a121")
Sheets("Sep").Range("a7:a121").Value =
Sheets("Sep").Range("a7:a121").Value
Sheets("Sep").Rows("7:121").Sort Key1:=Sheets("Sep").Range("a7")
Sheets("Oct").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Oct!B7,Summary!B:B,0 ))"
Sheets("Oct").Range("a7").AutoFill
Destination:=Sheets("Oct").Range("a7:a121")
Sheets("Oct").Range("a7:a121").Value =
Sheets("Oct").Range("a7:a121").Value
Sheets("Oct").Rows("7:121").Sort Key1:=Sheets("Oct").Range("a7")
Sheets("Nov").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Nov!B7,Summary!B:B,0 ))"
Sheets("Nov").Range("a7").AutoFill
Destination:=Sheets("Nov").Range("a7:a121")
Sheets("Nov").Range("a7:a121").Value =
Sheets("Nov").Range("a7:a121").Value
Sheets("Nov").Rows("7:121").Sort Key1:=Sheets("Nov").Range("a7")
Sheets("Dec").Range("a7").Formula =
"=INDEX(Summary!A:A,MATCH(Dec!B7,Summary!B:B,0 ))"
Sheets("Dec").Range("a7").AutoFill
Destination:=Sheets("Dec").Range("a7:a121")
Sheets("Dec").Range("a7:a121").Value =
Sheets("Dec").Range("a7:a121").Value
Sheets("Dec").Rows("7:121").Sort Key1:=Sheets("Dec").Range("a7")
End If

'colors start here
If Target.Column < 6 Or Target.Count 1 Then Exit Sub
Select Case Target.Value
Case "Admin"
iClr = 2
Case "C&I - Dual Seat"
iClr = 3
Case "C&I - Typing"
iClr = 3
Case "Engineering"
iClr = 46
Case "Packaging"
iClr = 6
Case "Plant"
iClr = 4
Case "Policy"
iClr = 5
Case "Resale - Exam"
iClr = 8
Case "Resale - Search"
iClr = 8
Case "Resale - Type"
iClr = 8
Case "Single Seat - SL"
iClr = 40
Case "SD - Dual Seat"
iClr = 15
Case "SD - Type"
iClr = 15
Case "SD - Other"
iClr = 15
Case "Order Needs"
iClr = 7
Case Else
iClr = -4142 'no color
End Select

Target.Offset(0, -4).Interior.ColorIndex = iClr

End Sub
--------------------


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile:
http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=504175



Reply With QuoteReply With Quote