(Solved) – Excel Loop Through all filled cells in row 1

(solved)-–-excel-loop-through-all-filled-cells-in-row-1

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

Below is a sample of how i want the Calculator sheet to look like. Let me know if there’s anything unclear in my description. Thanks in advance! sample image of calculator

Leave a Reply

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