Countif in VBA 2

[cbanks]

Here is code i have previously used.

For Each ce In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
        ce.Offset(0, 1) = WorksheetFunction.CountIf(Range("k2:k"), ce.Value)
    Next ce

However, now my data is on a seperate spreadsheet. Column A i want to use is on sheet "sales rep" and col k i want to use is on sheet "Data Sheet". I cannot seem to grasp how to make this compare col a from one sheet to col k on another. Any suggestions? Thanks.

 

[SkipVought]

Hi,

For Each ce In sheets("sales rep").Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
        ce.Offset(0, 1) = WorksheetFunction.CountIf(sheets("Data Sheet").Range("k2:k"), ce.Value)
Next ce

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[PHV]

Have you tried something like this ?
With Worksheets("sales rep")
For Each ce In .Range("a2:a" & .Cells(.Rows.Count, 1).End(xlUp).Row)
ce.Offset(0, 1) = WorksheetFunction.CountIf(Worksheets("Data Sheet").Range("k2:k"), ce.Value)
Next ce
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cbanks]

Thanks a ton!

 

[cbanks]

Yes they both work. Im actually using this one.

For Each ce In Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
ce.Offset(0, 2) = WorksheetFunction.CountIf(Worksheets("Data Sheet").Range("k:k"), ce.Value)


Next ce

However, now im getting a little more complex. If those cells match then I need it to count the corresponding cell in col BW sheet "Data sheet" that contains a 1.

 

[PHV]

I don't think Skip's suggestion works if "sales rep" is not the active sheet ...

 

[cbanks]

Yes, i didnt post it before but im using the

With Worksheets ("Sales Reps")

 

[PHV]

With all the dots I suggested ?
With Worksheets("sales rep")
For Each ce In [highlight].[/highlight]Range("a2:a" & [highlight].[/highlight]Cells([highlight].[/highlight]Rows.Count, 1).End(xlUp).Row)
ce.Offset(0, 1) = WorksheetFunction.CountIf(Worksheets("Data Sheet").Range("k2:k"), ce.Value)
Next ce
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cbanks]

Yes with all the dots? Have you checked my post above about needing to add to my code?

 

[SkipVought]

PHV is correct.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[PHV]

Have you checked my post above about needing to add to my code?
No, I didn't see it as we posted at same time ...

 

[cbanks]

Can you look at it? im having great difficulty coming up with a solution. Very sorry if I seem rude im just new to this forum. Sorry, im just learning. Thanks for your help

 

[SkipVought]

You are assigning a VALUE (a count from COUNTIF)

Where is the MATCH?

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[PHV]

No offense at all.
Welcome to Tek-Tips.
I'll try to understand your new thread and post a guess (if not already done by skip ;-))

 

[cbanks]

ok. well im having problems communicating what i need. I simply need for my program to stop counting how many times each number in col a repeats itself in col k, and to start counting how many times col a repeats itself in col k and there is a 1 in corresponding cell in col BW