using match function

[AD108]

I tried using the following code, but the match function is returning
nothing. Im stuck on this one. Any ideas out there?


Option Explicit

Sub total_count()

Dim rngSub1 As Range
Dim rngSub2 As Range
Dim Cell As Range
Dim i As Integer
Dim x As Range
Dim y As Integer

Set rngSub1 = Sheets(1).Range("F:F").SpecialCells(xlCellTypeConstants, 23)
Set rngSub2 = Sheets(1).Range("C:C").SpecialCells(xlCellTypeConstants, 23)

For Each Cell In rngSub1
On Error Resume Next
x = Application.WorksheetFunction.Match(Cell, rngSub2, 0)
Debug.Print x
If Not x Is Nothing Then
y = x.Offset(0, 1).Value
y = y + 1
x.Offset(0, 1).Value = y
Else: MsgBox "NO MATCH"
End If

Next Cell
End Sub

 

[Bob Phillips]

Try this

Sub total_count()

Dim rngSub1 As Range
Dim rngSub2 As Range
Dim Cell As Range
Dim i As Integer
Dim x As Long
Dim y As Integer

Set rngSub1 = Sheets(1).Range("F:F").SpecialCells(xlCellTypeConstants, 23)
Set rngSub2 = Sheets(1).Range("C:C").SpecialCells(xlCellTypeConstants, 23)

For Each Cell In rngSub1
On Error Resume Next
x = 0
x = Application.WorksheetFunction.Match(Cell, rngSub2, 0)
Debug.Print x
If x <> 0 Then
y = rngSub2.Cells(x, 2).Value
y = y + 1
rngSub2.Cells(x, 2).Value = y
Else: MsgBox "NO MATCH"
End If

Next Cell
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)