Code not working

I did not write this code.  Can someone tell me please what it is doing.  Is it looking for duplicates???

Sub AdRec
Dim r As Range, rw As Long
 
Set r = Sheet19.Range("A1")

If r(3, 1) <> "" Then
rw = FindRec("Cusip_tmp", 1, cusip)
r.Offset(rw, col) = r.Offset(rw, col) + 1

End Sub
__________________________________________
Function FindRec(w As String, numcol As Integer, lookval As String) As Long
Dim r As Range, numrows As Long
Dim I As Long

numrows = Worksheets(w).UsedRange.Rows.Count
Set r = Worksheets(w).Range("A1").Offset(0, numcol - 1).Resize(numrows, 1)
 On Error Resume Next
 I = -999
 I = WorksheetFunction.Match(lookval, r, 0)
 On Error GoTo 0
 
FindRec = I - 1
leezacAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
As posted, the code will likely fail because:
1. No End If in sub AdRec
2. Variables col and cusip not declared in AdRec. Nor do they appear to have values.
3. Sheet19 is a codename, not a tabname. If you don't know the difference, you should be using the tabname instead.
4. In function FindRec, the code will throw the user into the debugger if a match isn't found.
5. The value for numrows is reliable only if the data starts on row 1
6. There is no test for a negative value of rw when FindRec returns to AdRec. A run-time error will result if that happens.

I've rewritten the code to avoid the above issues. I believe that it is checking worksheet Cusip_tmp column A for the value of cusip. FindRec returns the row number -1 of that match. Adrec then increments the value of a certain cell in that row by 1.
Sub AdRec()
Dim r As Range
Dim col As Long, rw As Long
'col=1      'Need a value for col. It probably shouldn't be 1
Set r = Worksheets("Cusip_tmp").Range("A1")

If r(3, 1) <> "" Then
    rw = FindRec("Cusip_tmp", 1, "cusip")
    If rw > 0 Then
        r.Offset(rw, col) = r.Offset(rw, col) + 1
    End If
End If
End Sub
__________________________________________
Function FindRec(w As String, numcol As Integer, lookval As String) As Long
Dim r As Range, numrows As Long
Dim I As Long

numrows = Worksheets(w).UsedRange.Row + Worksheets(w).UsedRange.Rows.Count - 1
Set r = Worksheets(w).Range("A1").Offset(0, numcol - 1).Resize(numrows, 1)
 On Error Resume Next
 I = -999
 I = WorksheetFunction.Match(lookval, r, 0)
 On Error GoTo 0
 
FindRec = I - 1
End Function

Open in new window

0
 
leezacAuthor Commented:
Thank you!
0
All Courses

From novice to tech pro — start learning today.