CODEWITHSUNDEEP
excelvbacountif
Renee Fong
Renee Fong

Reputation: 1

Problem referencing from another workbook using VBA because I want to do countif

I just begin to learn VBA and this is my assignment question. It requires me to use a range of data from my workbook named 17067513_Excel. I have to use COUNTIF to find out how many cells contain more than 40 marks. Can you look at my code and help me 

Sub Test()
    Dim iVal As Integer
    iVal = Application.WorksheetFunction.CountIf(Workbooks("17067513_Excel.xlsx").Worksheets("17067513").Range("N2:N296"), ">40%")
Sheets("VBA").[B1] = iVal
End Sub

Upvotes: 0

Views: 413

Answers (1)

Error 1004
Error 1004

Reputation: 8240

If you want to write a code to calculate COUNTIF and both data ( the range you need for the calculation) & code included in the same workbook you can try the below:

Option Explicit

Sub Countif()

    Dim Result As Long
    Dim rng As Range

    Set rng = ThisWorkbook.Worksheets("1706751").Range("N2:N296")

    Result = Application.WorksheetFunction.Countif(rng, ">40%")

End Sub

Note: Save you workbook as Excel Macro-Enabled Workbook (*.xlsm)

Upvotes: 0

Related Questions