Need to properly dimension and define variable range for use in CountIf

Hi,

I am having trouble setting up my variable range for use in a CountIf.

First I select column A. Then go through each value in the column, looking for duplicates. I determine if it is a duplicate by checking all the cells above it for a match. The If statement should be false for the first value and true for all duplicate values, hence the "If countif > 1". I believe the problem is the variable range.

Here is my code. Thanks for any help.

PHP Code: 

Sub T()

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Dim x As Range
Dim y As Long
Dim Rng As String


 For Each x In Selection
  y = x.Row
  Rng = ("A2:A" & y)
  If Application.CountIf(Rng, "x") > 1 Then
  x.Offset(0, 8) = 1
  End If
 Next x
End Sub 


Andy,

I tried that and the error is "Type Mismatch" for the Rng in CountIf.

Because first argument in countif function must be Range data type and your is String

When I change the string to a range... The error message "Object variable or With block variable not set" pops up for Rng = ("A2:A" & y).

Because your Rng must be like this:

Please Login or Register  to view this content.

Thanks for the help so far, but although the code is free of errors, the IF statement is never true. For example when cells A2 and A3 have a value of 50, the statement is not true. In that case it should be true for A3.

AndyLitch in post #4 has the solution, to my eye. Be sure to make that edit. Show us your resulting code if it still isn't working.

This is my latest, but still not perfect code...

When I step through it, I notice the If statement is never true.

PHP Code: 

Sub T()

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Dim x As Range
Dim y As Long
Dim Rng As Range


 For Each x In Selection
  y = x.Row
  Set Rng = Range("A2:A" & y)
  If WorksheetFunction.CountIf(Rng, "x") > 0 Then
  x.Offset(0, 8) = 1
  End If
 Next x
End Sub 


Oops. The > should be 1. I modified it to see if I could get it to be true.

Here it is with > 1

PHP Code: 

Sub T()

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Dim x As Range
Dim y As Long
Dim Rng As Range


 For Each x In Selection
  y = x.Row
  Set Rng = Range("A2:A" & y)
  If WorksheetFunction.CountIf(Rng, "x.value") > 1 Then
  x.Offset(0, 8) = 1
  End If
 Next x
End Sub 


remove quot marks from "x.value"

YES! Yes yes yes!
Thank you to Sbarro79, AndyLitch and JBeaucaire!

I've tripled my VBA knowledge and productivity in just a few days because of helpful people like you.

Does this accomplish the same thing? It puts a regular formula into column I, all cells at once, then removes the formula leaving the results behind, no looping.

Please Login or Register  to view this content.

Yes, brilliant. Ill use that one in my code. Im a beginner so looping was easier for me to understand.

But now Ive learned a new method!

Thanks again