Runtime Error 9

[Bishop]

I have the following code:

Dim G as Integer

G = Worksheets("Movies").WorksheetFunction.CountIf("AE:AE", "G")

I'm assuming I'm simply not using the CountIf function correctly but I keep
getting Subscript Out Of Range. What am I doing wrong?

 

[Mike H]

Hi,

Try this but note your criteria of G was invalid and subscriot out of range
could mean your sheet name Movies is spelt incorrectly

G = WorksheetFunction.CountIf(Sheets("Movies").Range("AE:AE"),
Sheets("Movies").Range("G1"))

Mike

 

[Bishop]

Still getting the same error. And I verified that the sheet name is spelled
correctly. Other ideas?

 

[Dave Peterson]

Maybe the activeworkbook isn't what you expected.

Or your worksheet name isn't spelled correctly (check for leading/trailing
spaces, too!)

 

[ytayta555]

 Other ideas?

I think it cann't Dim G as Integer ,
maybe Dim G As Worksheet .

 

[Mike H]

No more ideas, it works perfectly for me and subscript out of range is Excels
way of telling you it can't find something your telling it to look for

Mike

 

[ytayta555]

In the same time , Countif is looking for
criteria ,, G ,, , which have the same name
with your G variable , rename variable G with
another name .

 

[Bishop]

That was it! When I made the Movies worksheet active (it wasn't before) it
worked. But I need this in a With block. I tried this:

With Worksheets("Movies")
G = .WorksheetFunction.CountIf(Sheets("Movies").Range("AE:AE"),
Sheets("Movies").Range("G1"))

But that's not working. Will this work in a With block?

 

[Mike H]

hi,

However you got my code to work it wasn't by making Movies the active sheet
because it doesn't need to be active if the sheet is explicitly named in the
code. the correct syntax for what your trying to do now is

With Worksheets("Movies")
G = WorksheetFunction.CountIf(.Range("AE:AE"), .Range("G1"))
End With


Mike