0

I have a workbook that opens another workbook (Envision). I am then trying to use WorkSheet function CountIf in 'Envision' using a dynamic range (Drw_Search_Rng ). I keep getting Type mismatch error. What I am doing wrong?

    Dim EnViSioN As New Excel.Application

    'Opens Envision # list
    EnViSioN.Workbooks.Open FileName:=FilePath, ReadOnly:=True

'Sets Range to search for drawing #: For eg. EnViSioN.Sheets(1).Range("A1:A26")
    Set Drw_Search_Rng = EnViSioN.Sheets(1).Range(ColLett(DrWNum_Col) & "1:" & ColLett(DrWNum_Col) & Envision_LastRow)

'Check how many times DrWNum was found in the Drw_Search_Rng
    DrwNum_Hits = Application.WorksheetFunction.CountIf(Drw_Search_Rng, "*" & DrWNum & "*")

Thanks, Mahmoud

Community
  • 1
  • 1
M. Marzouk
  • 137
  • 2
  • 15
  • I guess because a sheet is a property of a workbook rather than the application itself. (There could be other issues but you haven't posted all your code.) – SJR Mar 27 '17 at 22:02
  • Yeah, that fixed it. `WorksheetFunction.CountIf(Drw_Search_Rng, "*" & DrWNum & "*")`. `Application.WorksheetFunction` usually works when I use it within the workbook. Thanks – M. Marzouk Mar 27 '17 at 22:06

0 Answers0