[VBA] Countif with For loop

[HaHa1712]

How to transform the code below into VBA Code (or another method)?
IF(COUNTIF(Data!$1:$1,A2)=0,0,COUNTIF(Data!$1:$1,B1))+
IF(COUNTIF(Data!$2:$2,A2)=0,0,COUNTIF(Data!$2:$2,B1))+
IF(COUNTIF(Data!$3:$3,A2)=0,0,COUNTIF(Data!$3:$3,B1))+
.
.
.
IF(COUNTIF(Data!$10000:$10000,A2)=0,0,COUNTIF(Data!$10000:$10000,B1))

Thanks.

 

[Virici]

Not sure what exactly you are asking for.

If what you need is a code that checks if a sheet named Data has any values in row 1:10000 that matches A2 (in another sheet) and if said is true, sums the counts of matches to B1, then try this:
Note: I assume that the values A2 and B1 in your formulas are static values in a sheet called "LOOKUP". Change accordingly.

Sub Test()


Dim num As Long


num = 0


For i = 1 To 10000
    If Application.WorksheetFunction.CountIf(Sheets("Data").Range("$" & i & ":$" & i), Sheets("LOOKUP").Cells(2, 1).Value) = 0 Then
        GoTo Cont
    Else
        n = n + Application.WorksheetFunction.CountIf(Sheets("Data").Range("$" & i & ":$" & i), Sheets("LOOKUP").Cells(1, 2).Value)
    End If
Cont:
Next i


End Sub

Code not tested and I am afraid it might take some time to compute for 10,000 rows...

 

[Virici]

Seems I was a tad too quick with the code

Sub Test()


Dim num As Long


num = 0


For i = 1 To 10000
    If Application.WorksheetFunction.CountIf(Sheets("Data").Range("$" & i & ":$" & i), Sheets("LOOKUP").Cells(2, 1).Value) = 0 Then
        GoTo Cont
    Else
        num = num + Application.WorksheetFunction.CountIf(Sheets("Data").Range("$" & i & ":$" & i), Sheets("LOOKUP").Cells(1, 2).Value)
    End If
Cont:
Next i


MsgBox num


End Sub