CODEWITHSUNDEEP
vbaexcel
Nick123
Nick123

Reputation: 197

Object required error in VBA code

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!

Upvotes: 0

Views: 94

Answers (2)

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 0

QHarr
QHarr

Reputation: 84455

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

Upvotes: 1

Related Questions