WorksheetFunction.CountIf for multiple sheets

Question

I am using WorksheetFunction.CountIf to compare among two different work sheets but getting 1004 error

Sub sbWriteIntoCellData()
Dim CODE As Workbook
Dim Sheet1 As Sheets
Dim Sheet2 As Sheets
Set Sheet1 = Sheets
Set Sheet2 = Sheets
For Each rngCell In Worksheets("Sheet1").range("A2", range("A2").End(xlDown))

If WorksheetFunction.CountIf(Sheets("Sheet2").range("A2", range("A2").End(xlDown)), rngCell) = 1 Then

      Worksheets("Sheet1").range("C" & Rows.Count).End(xlUp).Offset(1) = "Yes"
        Else: Worksheets("Sheet1").range("C" & Rows.Count).End(xlUp).Offset(1) = "No"
    End If



Next

MsgBox "Execution completed"

End Sub

[ enter image description here

If WorksheetFunction.CountIf(Sheets("Sheet2").range("A2", range("A2").End(xlDown)), rngCell) = 1 Then Getting error at this line — srawan kallakuri, Sep 09 '19 at 10:48
you need to specify a worksheet for **every** range. There are – range objects that are not tied to a specific worksheet. Also what do you expect `Set Sheet1 = Sheets` to do? — Pᴇʜ, Sep 09 '19 at 11:23
Thanks for the suggestion.. Can you please explain with syntax — srawan kallakuri, Sep 09 '19 at 11:26

Pᴇʜ · Accepted Answer · 2019-09-09T11:37:29.563

0

Tidy up your code, use correct references and set your variables properly. Specify for every Range object in which sheet it is. Then you should not get any errors (of course I didn't check your code logic, but just corrected the obvious issues).

Option Explicit

Public Sub sbWriteIntoCellData()
    Dim Sheet1 As Worksheet
    Set Sheet1 = ThisWorkbook.Worksheets("Sheet1")

    Dim Sheet2 As Worksheet
    Set Sheet2 = ThisWorkbook.Worksheets("Sheet2")

    Dim Cell As Range
    For Each Cell In Sheet1.Range("A2", Sheet1.Range("A2").End(xlDown))
        If Application.WorksheetFunction.CountIf(Sheet2.Range("A2", Sheet2.Range("A2").End(xlDown)), Cell.Value) = 1 Then
            Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Offset(1, 0) = "Yes"
        Else
            Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp).Offset(1, 0) = "No"
        End If
    Next Cell

    MsgBox "Execution completed"
End Sub

edited Sep 09 '19 at 11:37

answered Sep 09 '19 at 11:32

Pᴇʜ

56,719
10
49
73

Thanks a lot sir... I bow my head to you patience..... I am new to coding world and didn't even understand some basics..... Thanks a lot for standing beside me in this heard journey... – srawan kallakuri Sep 09 '19 at 11:35
The code is perfectly working for me... But when I try a large set around 500k(5-lakhs) set of records comparing with some 5000 (max count) it is executing very slow (Not responding even after 15 min) and i closed the excel forcefully... Is there any way to cut the execution time... Please help – srawan kallakuri Sep 09 '19 at 12:31
if it is v-lookup formula the execution time is less than 2 min for almost 1.2 million records comparing with 200 (even up to 5k) from different work books.... The formula is mentioned below.... IF(OR(ISNA(VLOOKUP(F2,'[Value Sets.xlsx]Value Sets'!$AE$2:$AE$130,1,FALSE))=FALSE, ISNA(VLOOKUP(L2,'[Value Sets.xlsx]Value Sets'!$AE$2:$AE$130,1,FALSE))=FALSE, ISNA(VLOOKUP(M2,'[Value Sets.xlsx]Value Sets'!$AE$2:$AE$130,1,FALSE))=FALSE, ISNA(VLOOKUP(E2,'[Value Sets.xlsx]Value Sets'!$AE$2:$AE$130,1,FALSE))=FALSE),"Yes","No") – srawan kallakuri Sep 09 '19 at 12:33
But it will execute in every single cell and i need to drag every time for different sets of data comparison.. This is why I am looking for a VBA code – srawan kallakuri Sep 09 '19 at 12:35
If you can use formulas use formulas they are almost always faster than VBA. – Pᴇʜ Sep 09 '19 at 12:35
Okay Sir... Is there a way to execute the same formula using VBA? – srawan kallakuri Sep 09 '19 at 12:41

score 0 · Answer 2 · answered Sep 09 '19 at 17:16

I have generated a code to pass v-lookup formula into VBA and it is working fine... I am posting my code(verified).. It might help some one who is looking for similar kind of solution

The code will work for comparing three different rows in sheet-1 with another row data in sheet-2 in or condition

Simply: Code will print "Yes" if the value in a2(sheet-2) exists in F2/H2/I2 from sheet-1 The result will be stored in AB2 in sheet-1

    Sub compare()
    Dim DataRange As Range
      Dim Sheet1 As Worksheet
        Set Sheet1 = ThisWorkbook.Worksheets("Sheet1")
        Dim Sheet2 As Worksheet
        Set Sheet2 = ThisWorkbook.Worksheets("Sheet2")
.Find the last cell value
     Dim Cell As Range
      Dim last As Double
    With ActiveSheet
            last = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
       Range("AB2").Formula = "=IF(OR(ISNA(VLOOKUP(F2,Sheet2!$A$2:$A$5,1,FALSE))=FALSE,ISNA(VLOOKUP(H2,Sheet2!$A$2:$A$5,1,FALSE))=FALSE,ISNA(VLOOKUP(I2,Sheet2!$A$2:$A$5,1,FALSE))=FALSE),""Yes"",""No"")"
    Range("AB2").AutoFill Destination:=Range("AB2:AB" & last)
    MsgBox "Execution completed"
    End Sub

score 0 · Answer 3 · answered Sep 09 '19 at 18:36

Using R1C1 notation can both increase processing time and also make the code easier to maintain.

Option Explicit

Public Sub sbWriteIntoCellData()

    Dim sht1 As Worksheet
    Set sht1 = ThisWorkbook.Worksheets("Sheet1")

    With sht1

        Dim lastRow As Long
        lastRow = .Range("A2").End(xlDown).Row

        Dim res As Range
        Set res = .Range("C2:C" & lastRow)

        With res
                                                 '-2 bc formula on column C and checking against column A from sheet2
            .FormulaR1C1 = "=If(COUNTIF(Sheet2!C[-2],RC[-2])>1,""Yes"",""No"")"
            .Calculate
            .Value = .Value
        End With

    End With

End Sub

Hey one small question... Can we include dynamic range in the v lookup formula instead of $A$2:$A$5 — srawan kallakuri, Sep 10 '19 at 05:34

WorksheetFunction.CountIf for multiple sheets

3 Answers3