VBA Smaller Code 3

Z

ziad alsayed

Well-known Member
Joined
Jul 17, 2010
Messages
665
dear all

the below code is workin fine. i just need to reduce it
Appreciate any help

Code: 
Sub try()
Dim wb As Workbook
Set wb = ActiveWorkbook
Worksheets("Detailed Report").Select
If Cells(1, 1) = "DecemberP" Then
Worksheets("2011").Select
Cells(5, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(5, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(5, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(5, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(5, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(5, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "January" Then
Worksheets("2011").Select
Cells(6, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(6, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(6, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(6, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(6, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(6, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "February" Then
Worksheets("2011").Select
Cells(7, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(7, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(7, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(7, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(7, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(7, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "March" Then
Worksheets("2011").Select
Cells(8, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(8, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(8, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(8, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(8, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(8, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "April" Then
Worksheets("2011").Select
Cells(9, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(9, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(9, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(9, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(9, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(9, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "May" Then
Worksheets("2011").Select
Cells(10, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(10, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(10, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(10, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(10, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(10, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "June" Then
Worksheets("2011").Select
Cells(11, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(11, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(11, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(11, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(11, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(11, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "July" Then
Worksheets("2011").Select
Cells(12, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(12, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(12, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(12, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(12, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(12, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "August" Then
Worksheets("2011").Select
Cells(13, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(13, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(13, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(13, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(13, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(13, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "September" Then
Worksheets("2011").Select
Cells(14, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(14, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(14, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(14, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(14, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(14, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "October" Then
Worksheets("2011").Select
Cells(15, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(15, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(15, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(15, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(15, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(15, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "November" Then
Worksheets("2011").Select
Cells(16, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(16, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(16, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(16, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(16, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(16, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
ElseIf Cells(1, 1) = "December" Then
Worksheets("2011").Select
Cells(17, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
Cells(17, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
Cells(17, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
Cells(17, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
Cells(17, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
Cells(17, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
Worksheets("Detailed Report").Select
End If
End Sub

thanks in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
This is very similar to the smaller code 1 post I helped you on a bit ago:

Code: 
Sub try()
Dim MonthArray  As Variant, _
    xCol        As Variant
' Variable Initialization
MonthArray = Array("DecemberP", "January", "February", "March", "April", "May", "June", _
                   "July", "August", "September", "October", "November", "December")
 
Application.ScreenUpdating = False
xCol = Application.Match(Worksheets("Detailed Report").Cells(1, 1).Value, MonthArray, 0)
If Not IsError(xCol) Then
    With Worksheets("2011")
        .Cells(xCol + 4, 24).FormulaR1C1 = "=COUNTIFS(Total!C[-15],Expatriate,Total!C[-18],PH)"
        .Cells(xCol + 4, 25).FormulaR1C1 = "=COUNTIFS(Total!C[-16],Expatriate,Total!C[-19],Apapa)"
        .Cells(xCol + 4, 26).FormulaR1C1 = "=COUNTIFS(Total!C[-17],Expatriate,Total!C[-20],VI)"
        .Cells(xCol + 4, 27).FormulaR1C1 = "=COUNTIFS(Total!C[-18],Expatriate,Total!C[-21],Kano)"
        .Cells(xCol + 4, 28).FormulaR1C1 = "=COUNTIFS(Total!C[-19],Expatriate,Total!C[-22],Ikeja)"
        .Cells(xCol + 4, 29).FormulaR1C1 = "=COUNTIFS(Total!C[-20],Expatriate,Total!C[-23],Assembly)"
    End With
Else
    MsgBox "Cell A1 does not contain a valid value."
End If
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Another possibility, but possibly a bit slower. Remember, shorter code is not always more effecient. :biggrin:

Code: 
Sub try()
Dim MonthArray  As Variant, _
    wsArray     As Variant, _
    xCol        As Variant, _
    i           As Long
' Variable Initialization
MonthArray = Array("DecemberP", "January", "February", "March", "April", "May", "June", _
                   "July", "August", "September", "October", "November", "December")
 
wsArray = Array("PH", "Apapa", "VI", "Kano", "Ikeja", "Fabrication", "Assembly")
Application.ScreenUpdating = False
xCol = Application.Match(Worksheets("Detailed Report").Cells(1, 1).Value, MonthArray, 0)
If Not IsError(xCol) Then
    With Worksheets("2011")
        For i = 24 To 29
            .Cells(xCol + 4, i).FormulaR1C1 = "=COUNTIFS(Total!C9,Expatriate,Total!C6," & wsArray(i - 24) & ")"
        Next i
    End With
Else
    MsgBox "Cell A1 does not contain a valid value."
End If
Application.ScreenUpdating = True
End Sub
 
Upvote 0
dear MrKowz

I was really shy to as you again, you are a humble person. thanks.
you are right, it is similar, it will take me time to understand the first code.
i will try to understand both code also for VBA Smaller Code 3
 
Upvote 0
ziad alsayed said:
dear MrKowz

I was really shy to as you again, you are a humble person. thanks.
you are right, it is similar, it will take me time to understand the first code.
i will try to understand both code also for VBA Smaller Code 3
Click to expand...

No worries - I'm a volunteer here and I like to help out where I can. ;)
 
Upvote 0
thanks MrKows,
i learned VBA from books and Podcast.
though i have many exel Books like excel 2007 power programming VBA, VBA and Macro for Mr excel 2007, i also have Mr excel VBA video,also the complete VBA bundle from Mr excel but all those books,DVD's.... don't have such code.
i did a lot of research through the web, but all are simlilar level or less than the books i have.

i have maybe all the link that excel guru's has or suggest it.

i even discovered a mistake in the excel 2007 Bible for John walkenbanch :)
beleive me for my level in VBA i did too much within 5 to 6 months

any idea or resources from where i can learn how to write such code?
i am also ready to pay for such service, my purpose is understand the concept.
i Kow i am still Weak in Array

Note that Excel is a Hobby for me and i really like it.
Appreciate any help.
 
Upvote 0
I've learned most everything I know from the forums here (it's a hobby for myself as well. Hoping to one day open my own consulting business). I took two classes in college that focused on doing actuarial calculations and forms in Excel (but honestly, it was still relatively basic concepts). Just stick around, poke your head into some posts that pique your interest, and try to play with (and understand) code. It is a slow, but rewarding, process.
 
Upvote 0
thanks for the advise, i will work towards that.

please note that the shorter code is not giving the record for the "Assembly", what could be the reason??
 
Upvote 0
I didn't notice that you had omitted Fabrication from those calculations. Change wsArray line to:

Code: 
wsArray = Array("PH", "Apapa", "VI", "Kano", "Ikeja", "Assembly")
 
Upvote 0
you are right , thanks.
what is the meaning of "wsArray(i - 24)" and why it is -24
by pressing F8 the first time is pass the code , it show "PH", but 24-24 is Zero

Does it mean Array with base Zero.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Rank Function
Replies
4
Views
364
vmjan02
V
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
386
Asbestos_Jen
Asbestos_Jen
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
592
RoryA
RoryA
D
  • Solved
Calculating the total interest paid on a car lease
Replies
4
Views
384
Domenic
D
V
  • Question Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
433
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,566
Messages
6,179,551
Members
452,927
Latest member
rows and columns

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top