Speed Up VBA code

dlatha01

Board Regular
Joined
Apr 7, 2009
Messages
65
Hi guys,

any help would be appreciated to speed up the below code.

I guess it would be around the countifs ?

Many Thanks
David

'If July HC 2014 is selected
Application.ScreenUpdating = False
Sheets("Headcount").Range("B6:H19").ClearContents
Range("K6:Q19").ClearContents
Range("B25:H42").ClearContents
Range("K25:Q42").ClearContents
Range("A1").Select
If Range("AB16") = 1 Then
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y""))"
Range("B6").Copy Range("b6:B19")
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-2],rank,R5C3,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-2],rank,R5C3,gender,R3C2,JulyHC,""Y""))"
Range("C6").Copy Range("c6:c19")
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-3],rank,R5C4,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-3],rank,R5C4,gender,R3C2,JulyHC,""Y""))"
Range("D6").Copy Range("d6:d19")
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-4],rank,R5C5,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-4],rank,R5C5,gender,R3C2,JulyHC,""Y""))"
Range("E6").Copy Range("e6:E19")
Range("F6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-5],rank,R5C6,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-5],rank,R5C6,gender,R3C2,JulyHC,""Y""))"
Range("F6").Copy Range("f6:f19")
Range("G6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-6],rank,R5C7,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-6],rank,R5C7,gender,R3C2,JulyHC,""Y""))"
Range("G6").Copy Range("g6:g19")
Range("H6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-7],rank,R5C8,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-7],rank,R5C8,gender,R3C2,gender,R3C2,JulyHC,""Y""))"
Range("H6").Copy Range("h6:h19")
Range("K6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-1],rank,R5C11,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-1],rank,R5C11,gender,R3C11,JulyHC,""Y""))"
Range("K6").Copy Range("k6:k19")
Range("L6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-2],rank,R5C12,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-2],rank,R5C12,gender,R3C11,JulyHC,""Y""))"
Range("L6").Copy Range("l6:l19")
Range("m6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-3],rank,R5C13,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-3],rank,R5C13,gender,R3C11,JulyHC,""Y""))"
Range("m6").Copy Range("m6:m19")
Range("n6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-4],rank,R5C14,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-4],rank,R5C14,gender,R3C11,JulyHC,""Y""))"
Range("n6").Copy Range("n6:n19")
Range("o6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-5],rank,R5C15,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-5],rank,R5C15,gender,R3C11,JulyHC,""Y""))"
Range("o6").Copy Range("o6:eek:19")
Range("p6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-6],rank,R5C16,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-6],rank,R5C16,gender,R3C11,JulyHC,""Y""))"
Range("p6").Copy Range("p6:p19")
Range("q6").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(location,RC[-7],rank,R5C17,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-7],rank,R5C17,gender,R3C11,JulyHC,""Y""))"
Range("q6").Copy Range("q6:q19")
Range("B25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-1],rank,R24C2,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-1],rank,R24C2,gender,R22C2,JulyHC,""Y""))"
Range("B25").Copy Range("b25:b42")
Range("C25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-2],rank,R24C3,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-2],rank,R24C3,gender,R22C2,JulyHC,""Y""))"
Range("c25").Copy Range("c25:c42")
Range("d25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-3],rank,R24C4,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-3],rank,R24C4,gender,R22C2,JulyHC,""Y""))"
Range("d25").Copy Range("d25:d42")
Range("e25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-4],rank,R24C5,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-4],rank,R24C5,gender,R22C2,JulyHC,""Y""))"
Range("e25").Copy Range("e25:e42")
Range("f25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-5],rank,R24C6,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-5],rank,R24C6,gender,R22C2,JulyHC,""Y""))"
Range("f25").Copy Range("f25:f42")
Range("g25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-6],rank,R24C7,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-6],rank,R24C7,gender,R22C2,JulyHC,""Y""))"
Range("g25").Copy Range("g25:g42")
Range("h25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-7],rank,R24C8,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-7],rank,R24C8,gender,R22C2,JulyHC,""Y""))"
Range("h25").Copy Range("h25:h42")
Range("K25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-1],rank,R24C11,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-1],rank,R24C11,gender,R22C11,JulyHC,""Y""))"
Range("K25").Copy Range("k25:k42")
Range("L25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-2],rank,R24C12,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-2],rank,R24C12,gender,R22C11,JulyHC,""Y""))"
Range("l25").Copy Range("l25:l42")
Range("m25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-3],rank,R24C13,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-3],rank,R24C13,gender,R22C11,JulyHC,""Y""))"
Range("m25").Copy Range("m25:m42")
Range("n25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-4],rank,R24C14,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-4],rank,R24C14,gender,R22C11,JulyHC,""Y""))"
Range("n25").Copy Range("n25:n42")
Range("o25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-5],rank,R24C15,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-5],rank,R24C15,gender,R22C11,JulyHC,""Y""))"
Range("o25").Copy Range("o25:eek:42")
Range("p25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-6],rank,R24C16,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-6],rank,R24C16,gender,R22C11,JulyHC,""Y""))"
Range("p25").Copy Range("p25:p42")
Range("q25").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC[-7],rank,R24C17,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC[-7],rank,R24C17,gender,R22C11,JulyHC,""Y""))"
Range("q25").Copy Range("q25:q42")
Range("a1").Select
Range("B6:H19").Value = Range("B6:H19").Value
Range("k6:q19").Value = Range("k6:q19").Value
Range("b25:h42").Value = Range("b25:h42").Value
Range("k25:q42").Value = Range("k25:q42").Value
Range("a1").Select
Application.ScreenUpdating = True
MsgBox "Data has been updated"
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994
Might be easier telling us what this code does/show us the before and after.

Regards
Chris
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,515
Office Version
  1. 365
Platform
  1. Windows
One thing that may help a little is to get rid of most of your "SELECT" statements. Most of them are unnecessary, and just the result of using the Macro Recorder.

All your lines that have this structure:
Code:
[COLOR=#333333]Range("B6").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = _[/COLOR]
[COLOR=#333333]"=IF(COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y""))"[/COLOR]
[COLOR=#333333]Range("B6").Copy Range("b6:B19")[/COLOR]
Can be shortened like this:
Code:
[COLOR=#333333]Range("B6").[/COLOR][COLOR=#333333]FormulaR1C1 = _[/COLOR]
[COLOR=#333333]"=IF(COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC[-1],rank,R5C2,gender,R3C2,JulyHC,""Y""))"[/COLOR]
[COLOR=#333333]Range("B6").Copy Range("b6:B19")[/COLOR]
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
Copy your data and try these:

Code:
Range("B6:H19").FormulaR1C1 = _
"=IF(COUNTIFS(location,RC1,rank,R5C,gender,R3C2,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC1,rank,R5C,gender,R3C2,JulyHC,""Y""))"
Range("K6:Q19").FormulaR1C1 = _
"=IF(COUNTIFS(location,RC10,rank,R5C,gender,R3C11,JulyHC,""Y"")=0,""-"",COUNTIFS(location,RC10,rank,R5C,gender,R3C11,JulyHC,""Y""))"
Range("B25:H42").FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC1,rank,R24C,gender,R22C2,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC1,rank,R24C,gender,R22C2,JulyHC,""Y""))"
Range("K25:Q42").FormulaR1C1 = _
"=IF(COUNTIFS(Team,RC10,rank,R24C,gender,R22C11,JulyHC,""Y"")=0,""-"",COUNTIFS(Team,RC10,rank,R24C,gender,R22C11,JulyHC,""Y""))"




Range("B6:H19").Value = Range("B6:H19").Value
Range("k6:q19").Value = Range("k6:q19").Value
Range("b25:h42").Value = Range("b25:h42").Value
Range("k25:q42").Value = Range("k25:q42").Value
 

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994

ADVERTISEMENT

Comfy, so glad you posted that!

Been sat here scratching my head for about 30 minutes. I had it the same sort of way but my columns kept incrementing.
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
Comfy, so glad you posted that!

Been sat here scratching my head for about 30 minutes. I had it the same sort of way but my columns kept incrementing.

Let's just see if it's what the OP was after.

I do suspect though that there maybe a better formula, but without more info about what the other ranges are etc I'm unsure.

Edit:

One handy trick when writing R1C1 formulas is to utilise the immediate window.

typing
? ActiveSheet.Range("C7").FormulaR1C1

Will display the R1C1 formula that is in C7.
 
Last edited:

wrightyrx7

Well-known Member
Joined
Sep 15, 2011
Messages
994

ADVERTISEMENT

That is really handy, thank you.

I have never had a reason to write code using 'RC'. But this proves writing this way is very handy, the code I was writing had a couple of loops to try and achieve this.

Love learning something new. Now i just need to get my head round writing with 'RC'.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,523
Messages
5,529,334
Members
409,863
Latest member
stacy09
Top