Object required error in VBA code

Question

Objective: I have 1 Array of strings, 1 Array of worksheets. Want to count the number of occurrences of each string in each worksheet.

My code:

Option Explicit

Sub Checking()
    Dim banks() As String
    Dim countrysheets As Variant
    Dim sheet As Worksheet
    Dim bank As Variant
    Dim CountryList, BankList As String
    Dim count
    Dim bankcount, sheetcount As Integer

    CountryList = "CN,AU,India,Thai,TW,INDO-IDR,MY,PHP-LOCAL,SG,SK,NZ"

    BankList = "Merrill Lynch,UBS,Citigroup,BNP,Macquarie,Morgan Stanley,Deutsche Bank,HSBC,CLSA,JP Morgan,Credit Suisse, Nomura, Goldman Sachs"

    countrysheets = Split(CountryList, ",")        
    banks = Split(BankList, ",")

    sheetcount = 0        
    For Each sheet In countrysheets        
        bankcount = 0            

        For Each bank In banks            
            count = Application.sheets(sheet).WorksheetFunction.CountIf(sheet.Cells, bank)                
            ActiveCell.Offset(bankcount, sheetcount).Value = count                
            bankcount = bankcount + 1                
        Next

        sheetcount = sheetcount + 1                
    Next

End Sub

Why do I keep getting object required errors?

Thanks!

QHarr · Accepted Answer · 2018-07-26 04:27:16Z

countrysheets = Split(CountryList, ",") will generate a string array not a collection of worksheet objects hence your first error. Same with banks.

A For i = LBound to UBound Loop is faster with arrays. You can then use the generated list of sheet names to access sheets with ThisWorkbook.Worksheets(countrysheets(i)). The string name at the current array index is used as a reference.

When you don't declare a type then it is implicitly a variant. So lines like Dim bankcount, sheetcount As Integer. Only the last is an Integer and the others are Variant. And Integer risks overflow so go with Long.

Use the Worksheets, not Sheets, collection to avoid chart sheets. Though not a problem with named sheets if those names do not refer to a chart sheet.

When splitting banks on ",", be careful of any unwanted whitespace that may be left included in a bank name meaning you don't get the results you expect. I was looking at your last two banks names where there is leading white space.

It is Application.WorksheetFunction.Countif.

A re-write might look like:

Option Explicit
Public Sub Checking()
    Dim banks() As String, countrysheets() As String, CountryList As String, BankList As String
    Dim count As Long, bankcount As Long, sheetcount As Long

    CountryList = "CN,AU,India,Thai,TW,INDO-IDR,MY,PHP-LOCAL,SG,SK,NZ"
    BankList = "Merrill Lynch,UBS,Citigroup,BNP,Macquarie,Morgan Stanley,Deutsche Bank,HSBC,CLSA,JP Morgan,Credit Suisse,Nomura,Goldman Sachs"
    countrysheets = Split(CountryList, ",")
    banks = Split(BankList, ",")
    sheetcount = 0
    Dim i As Long, j As Long, ws As Worksheet
    For i = LBound(countrysheets) To UBound(countrysheets)
        Set ws = ThisWorkbook.Worksheets(countrysheets(i))
        bankcount = 0
          For j = LBound(banks) To UBound(banks)
            count = Application.WorksheetFunction.CountIf(ws.Cells, banks(j))
            ActiveCell.Offset(bankcount, sheetcount).Value = count
         bankcount = bankcount + 1
        Next
        sheetcount = sheetcount + 1
    Next
End Sub

DisplayName · Accepted Answer · 2018-07-26 07:42:52Z

You can use Sheets object that accepts an array as its argument to loop through a list of sheets

Option Explicit

Sub Checking()
    Dim banks As Variant, bank As Variant, countrysheets As Variant
    Dim sheet As Worksheet 
    Dim CountryList As String, BankList As String
    Dim count As Long, bankcount As Long, sheetcount As Long

    CountryList = "CN,AU,India,Thai,TW,INDO-IDR,MY,PHP-LOCAL,SG,SK,NZ"

    BankList = "Merrill Lynch,UBS,Citigroup,BNP,Macquarie,Morgan Stanley,Deutsche Bank,HSBC,CLSA,JP Morgan,Credit Suisse, Nomura, Goldman Sachs"

    countrysheets = Split(CountryList, ",")        
    banks = Split(BankList, ",")

    For Each sheet In Sheets(countrysheets)       
        bankcount = 0            

        For Each bank In banks            
            count = Application.WorksheetFunction.CountIf(sheet.UsedRange, bank)                
            ActiveCell.Offset(bankcount, sheetcount).Value = count                
            bankcount = bankcount + 1                
        Next

        sheetcount = sheetcount + 1                
    Next

End Sub

Collectives™ on Stack Overflow

Object required error in VBA code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related