CODEWITHSUNDEEP
vbaexcelmacros
user2965711
user2965711

Reputation: 77

Counting the number of Colourfilled cells

enter image description hereI am trying to find the number of colourfilled cells in B coloumn. I want to count and display the number of cour filled coloumns

But I am getting error :

Dim sum As Long
Dim count As Long
     sum = 0
count = 0
    strFileName = Application.GetOpenFilename("Excel files (*.xls*),*.xl*", Title:="Open data")
    Set Target = Workbooks.Open(strFileName)
    Set tabWS = Target.Worksheets("Tabelle1")

    ' lastrow = tabWS.Range("D" & tabWS.Rows.count).End(xlUp).Row  'Trigger Description starts from 2 row A coloumn
        lastrow = tabWS.Range("B" & tabWS.Rows.count).End(xlUp).Row  'Trigger Description starts from 2 row A coloumn
        For j = 2 To lastrow
        If tabWS.Cells(j, 2).Interior.ColorIndex = 4 Then
        sum = sum + tabWS.Cells(j, 8).value
        count = count + 1
        End If
        Next j

        MsgBox ("the value is" & sum)
        End sub

I am getting error for sum = sum +tabs.cell(j,8).value

I can't figure it out whyI am getting this error. Can any one give me a suggestion

Upvotes: 0

Views: 35

Answers (1)

Derek Drew
Derek Drew

Reputation: 44

It looks to me like you're opening the Workbook each time you use a method on tabWS. Try setting tabWS equal to the following instead: 

tabWS = Worksheets("Tabelle1")

Now when you're setting your lastrow and sum variables in the latter part of your code you won't be trying to open the workbook over and over again.

Edit (continued from comment below)*:

    lastrow = Worksheets("Tabelle1").Range("B" & Worksheets("Tabelle1").Rows.count).End(xlUp).Row  
    For j = 2 To lastrow
    If Worksheets("Tabelle1").Cells(j, 2).Interior.ColorIndex = 4 Then
    sum = sum + Worksheets("Tabelle1").Cells(j, 8).value
    count = count + 1
    End If
    Next j

    MsgBox ("the value is" & sum)
    End sub

Upvotes: 1

Related Questions