(Solved) – Macro to Set Range value based on a variable


Hi’m new to VBA and i’m trying to work on a QA report(call audit) that will automatically calculate values based on the data of another worksheet. I have 2 Sheets: Elements which contains the data, and AutoMacro which shows the number of cells that meets a specific criteria.

The Elements sheet has columns named Element and Rate, and WeekNumber. I made a code that will list all distinct week numbers to the “AM” column of the Automacro Sheet. Now, i want to count how many calls got a rate of ‘Developing'(which is in column Rate of the Elements sheet) in Compassion(which is in column Element of the Elements sheet) on a specific week(AM column in the AutoMacro Sheet)

this code works fine:

CompDevCount = Application.WorksheetFunction.CountIfs( _
    Sheets("Elements").Range("f2:F1048576"), "Compassion", _
    Sheets("Elements").Range("G2:G1048576"),"Developing", _
    Sheets("Elements").Range("C2:C1048576"), "4") 

however, instead of putting a fixed value in the last criterion which is the weeknumber, i want my code to get the value of the corresponding cell in column AM(if macro is calculating values for row2 in the AutoMacro sheet, it should change the “4” to whatever value AM2 has) and i cant figured out how to do it.

any help is greatly appreciated

here’s a link to my sample file:

Leave a Reply

Your email address will not be published. Required fields are marked *