Use countif and a named range in VBA

[Wulluby]

Hi,


Looking for the correct way to translate =countif(u2r,cellvalue) to vba. u2r being a named range.


I currently have the following which has been bodged into different variations.

[pre]

Dim numRows As Integer
Dim c As Double

Range("A1").Select
numRows = ActiveCell.CurrentRegion.Rows.Count

For r = 2 To numRows
CelVal = Worksheets("Sheet1").Cells(r, 2).Value
c = Application.WorksheetFunction.CountIf(u2r, CelVal)

If c = 0 Then
Range("A" & r & ":I" & r).Copy
Sheets("Sheet2").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If

Next r

[/pre]

Thanks in advance.

 

[Luke M]

Within the VB code, it doesn't know that u2r is a range. It's treating it as a variable. Change the line to:

[pre]

c = Application.WorksheetFunction.CountIf(Range("u2r"), CelVal)

[/pre]
Also, the backtick mark to show code properly is located above the Tab key, not the single apostrophe next to the Return key. =)

 

[Wulluby]

Thanks Luke, and on the backtick mark too

 

[Luke M]

You're very welcome, Wulluby.