I’m sure this is possible, im just not sure what the code should be. i have 2 sheets: (1)Component which has all the Component Names where an analyst got marked down on, including dates of when the call occurred, and (2)Calculator, which counts the number of times a specific component appeared in a specific week number.
ive created a code which gets the distinct Component Names from the Component Sheet, and then copies and transpose them to the Calculator sheet. all the Component Names are in Row 1 starting from Column D1 then goes to E1, F1, and so on. i want row 2 to display the count or the number of times the component(listed in row 1) appeared in a week.
The code i have only works for columns, i do not know how to make it get the non-empty values of an entire row.
Public Sub CountComponent() Application.ScreenUpdating = False Sheets("Calculator").Unprotect Password:="secret" Set wsComponentData = Sheets("Components Data") Set wsCalculator = Sheets("Calculator") '//Get the index of the last filled row based on column A LastComponentRowIndex = wsComponentData.Cells(Rows.Count, "A").End(xlUp).Row '//Get Range for ComponentData Set ComponentRange = wsComponentData.Range("A2:A" & LastComponentRowIndex) '//Get the index of the last filled row based on column C LasttotalauditRowIndex = wsCalculator.Cells(Rows.Count, "C").End(xlUp).Row '//Get range for Calculator Set MyRange = wsCalculator.Range("C2:C" & LasttotalauditRowIndex) TotalCalls = WorksheetFunction.Sum(MyRange) '//Looping through all filled rows in the Components Data sheet For i = 2 To wsCalculator.Cells(Rows.Count, "A").End(xlUp).Row '//'THIS PART I WANT TO GET THE CELL VALUES IN ROW1 STARTING FROM D but i dont know how to Component = wsCalculator.Cells(i, "D").Value '//Count the # of calls that got hit in the corresponding Component If wsCalculator.Cells(i, "B").Value <> "" Then ComponentCount = Application.WorksheetFunction.CountIf( _ ComponentRange, Component) wsCalculator.Cells(i, "D").Value = ComponentCount End If Next End Sub