Using Counif to Reference Different WorkSheet

Mitchbvi

New Member
Joined
Mar 6, 2014
Messages
39
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

Code:
    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

Code:
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try resizing

Cells(x, 3) = Application.WorksheetFunction.CountIf(Worksheets(WorkS).Cells(3, 1).Resize(lRow-3, 1), ActiveCell.Offset(0, -1).Value)
 
Upvote 0
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.
 
Upvote 0
What version of xl are you using?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top