Type mismatch error in macro

[nikhil83]

Why is the following macro giving me a 'type mismatch' error on last statement ? INDIRECT(cname&""!G:G"") is supposed to return a coloumn in the sheet with name stored in cname.

Dim y As Integer
Dim cname As String

Sheets("Index").Select
cname = Range("e6").Value

ActiveCell.FormulaR1C1 = "=COUNTIFS(INDIRECT(cname&""!G:G""),""Y"",INDIRECT(cname&""!F:F""), Employee_db[Empl Name]])"
     
y = y + ActiveCell.Value

 

[NARAYANK991]

Hi Nikhil ,

There can be more than one reason for the error ; you should post what cname contains ( Range("E6") ) , and what the activecell on the Index tab contains when the penultimate line of code is executed.

If the activecell contains an error value , then ActiveCell.Value will generate an error.

Narayan