Runtime Error 9
 

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?

Runtime Error 9
 

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" wrote:

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?

Runtime Error 9
 

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

"Mike H" wrote:

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" wrote:

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?

Runtime Error 9
 

Maybe the activeworkbook isn't what you expected.

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

Bishop wrote:

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

"Mike H" wrote:

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" wrote:

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?

--

Dave Peterson

Runtime Error 9
 

On 1 Iul, 23:51, Bishop wrote:
*Other ideas?

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

Runtime Error 9
 

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

"Bishop" wrote:

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

"Mike H" wrote:

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" wrote:

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?

Runtime Error 9
 

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 .

Runtime Error 9
 

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?

"Dave Peterson" wrote:

Maybe the activeworkbook isn't what you expected.

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

Bishop wrote:

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

"Mike H" wrote:

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" wrote:

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?

--

Dave Peterson

Runtime Error 9
 

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

"Bishop" wrote:

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?

"Dave Peterson" wrote:

Maybe the activeworkbook isn't what you expected.

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

Bishop wrote:

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

"Mike H" wrote:

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" wrote:

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?

--

Dave Peterson