Making a table Macro More efficient

cimajc17

New Member
Joined
Jun 10, 2015
Messages
15
Hey everyone
I'm using this code to make a table for a graph to update by selection of data but I'm using a very inefficient method to do it. it works but I am wondering if there is a better way.

Range("B22").Select
ActiveCell.FormulaR1C1 = _
"=(COUNTIFS('Hidden graph sheet'!R2C46:R" & lastRowHiddengraphSheet2 & "C46,3,'Hidden graph sheet'!R2C45:R" & lastRowHiddengraphSheet2 & "C45,1)/COUNTIF('Hidden graph sheet'!R2C42:R" & lastRowHiddengraphSheet2 & "C42,1))*100"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"=(COUNTIFS('Hidden graph sheet'!R2C46:R" & lastRowHiddengraphSheet2 & "C46,2,'Hidden graph sheet'!R2C45:R" & lastRowHiddengraphSheet2 & "C45,1)/COUNTIF('Hidden graph sheet'!R2C42:R" & lastRowHiddengraphSheet2 & "C42,1))*100"
Range("D22").Select
ActiveCell.FormulaR1C1 = _
"=(COUNTIFS('Hidden graph sheet'!R2C46:R" & lastRowHiddengraphSheet2 & "C46,1,'Hidden graph sheet'!R2C45:R" & lastRowHiddengraphSheet2 & "C45,1)/COUNTIF('Hidden graph sheet'!R2C42:R" & lastRowHiddengraphSheet2 & "C42,1))*100"
Range("E22").Select
ActiveCell.FormulaR1C1 = _
"=(COUNTIFS('Hidden graph sheet'!R2C46:R" & lastRowHiddengraphSheet2 & "C46,4,'Hidden graph sheet'!R2C45:R" & lastRowHiddengraphSheet2 & "C45,1)/COUNTIF('Hidden graph sheet'!R2C42:R" & lastRowHiddengraphSheet2 & "C42,1))*100"
Range("B23").Select
ActiveCell.FormulaR1C1 = _
"=(COUNTIFS('Hidden graph sheet'!R2C46:R" & lastRowHiddengraphSheet2 & "C46,3,'Hidden graph sheet'!R2C45:R" & lastRowHiddengraphSheet2 & "C45,2)/COUNTIF('Hidden graph sheet'!R2C42:R" & lastRowHiddengraphSheet2 & "C42,1))*100"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"=(COUNTIFS('Hidden graph sheet'!R2C46:R" & lastRowHiddengraphSheet2 & "C46,2,'Hidden graph sheet'!R2C45:R" & lastRowHiddengraphSheet2 & "C45,2)/COUNTIF('Hidden graph sheet'!R2C42:R" & lastRowHiddengraphSheet2 & "C42,1))*100"
Range("D23").Select
ActiveCell.FormulaR1C1 = _
"=(COUNTIFS('Hidden graph sheet'!R2C46:R" & lastRowHiddengraphSheet2 & "C46,1,'Hidden graph sheet'!R2C45:R" & lastRowHiddengraphSheet2 & "C45,2)/COUNTIF('Hidden graph sheet'!R2C42:R" & lastRowHiddengraphSheet2 & "C42,1))*100"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"=(COUNTIFS('Hidden graph sheet'!R2C46:R" & lastRowHiddengraphSheet2 & "C46,4,'Hidden graph sheet'!R2C45:R" & lastRowHiddengraphSheet2 & "C45,2)/COUNTIF('Hidden graph sheet'!R2C42:R" & lastRowHiddengraphSheet2 & "C42,1))*100"


I am basically writing a slightly changed formula for each part of the table but i select a different range each time. Thanks
 

Forum statistics

Threads
1,078,277
Messages
5,339,237
Members
399,287
Latest member
Dave2804

Some videos you may like

This Week's Hot Topics

Top