Using Counif to Reference Different WorkSheet

[Mitchbvi]

I have been trying to use CountIf to retrieve data from a work sheet other than the one that provides the search criteria for the countif function. This works

    Cells(x, 3) = Application.WorksheetFunction.CountIf((Worksheets(WorkS).Range("A3:A1300")), ActiveCell.Offset(0, -1).Value)

However Cannot figure out what I need to replace the source range "A3:A1300" with. I use a function to call the Last row (LRow) on the worksheet "WorkS", but when I try this

Cells(x, 3) = Application.WorksheetFunction.CountIf(((Worksheets(WorkS).Range(Cells(3, 1), Cells(LRow, 1)))), ActiveCell.Offset(0, -1).Value)

I get a run time error 1004. Any suggestions greatly appreciated

Thanks

Peter

 

[mrhstn]

Is WorkS a variable or the actual sheet name?

 

[Mitchbvi]

Its a Variable

 

[mrhstn]

Try resizing

Cells(x, 3) = Application.WorksheetFunction.CountIf(Worksheets(WorkS).Cells(3, 1).Resize(lRow-3, 1), ActiveCell.Offset(0, -1).Value)

 

[Mitchbvi]

Try resizing

Cells(x, 3) = Application.WorksheetFunction.CountIf(Worksheets(WorkS).Cells(3, 1).Resize(lRow-3, 1), ActiveCell.Offset(0, -1).Value)

I apologies for the delay in acknowledging your response.
i had solved the problem by defining the range assigning it to a variable before calling the countif function.

Not it sure if one is allowed to ask a similar but different question so I apologize if in error.


the first bit of code counted the number of times a text string occurred I then wanted to sum the values of that same string. So I thought using sumifs would do the trick. However when I copied the original line and Changed countif to sumifs I was told that function did not exist. Not at my computer so cannot post the entire line. However it was the string I first posted with as I said countif changed to sumifs and of course the range and the offset for the result.

hope this all makes sense and once again my thanks for your help.

 

[Fluff]

What version of xl are you using?

 

[Mitchbvi]

What version of xl are you using?

My apologies took Christmas off Excel for Mac 2011

Happy New Year