VBA - Find row number or return row number of next empty cell-CodePudding

I'm struggling to get the code below to work and would really appreciate any support.

I can find the row number that contains a variable ID number - findRow which works fine.

I want to return the row number of the next available empty row if findRow = 0. I am using findNextRow to try and accomplish this, but clearly I am missing something.

Function findId(ids As String) As Integer
    Dim findRow As Range
    Dim findNextRow As Range
    
    Sheets("Data").Select
    Columns("A:A").Select
    
    Set findRow = Selection.Find(What:=ids, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
    Set findNextRow = Selection.Data.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        
    
    If Not findRow Is Nothing Then
        findId = findRow.Row
        
    ElseIf Not findRow Is Nothing Then
        findId = findNextRow.Row
        
    Else
        findId = 0
        
            End If
    
End Function

EDIT The function is being called on here:

Sub Import()
    Dim Drop As Worksheet
    Dim Data As Worksheet

    Set Drop = Worksheets("Drop")
    Set Data = Worksheets("Data")

    Dim idRow As Integer
    idRow = findId(Drop.Range("A2"))

    If (idRow = 0) Then
        MsgBox ("ID not found")
        End
    End If

    Drop.Range("A2:DS2").Copy
    Data.Range("A" & idRow & ":DS" & idRow).PasteSpecial xlPasteValues

    Call CopyPaste(Drop.Range("A" & idRow & ":R" & idRow), _
        Data.Range("A" & Rows.Count).End(xlUp).Offset(1, 0))

End Sub

I've tried using the updated function as suggested:

Function findId(ids As String) As Long
    Dim m
    With Sheets("Data")
        m = Application.Match(ids, .Columns("A"), 0) 'any match on id?
        'got a match?  If not get next empty cell
        If IsError(m) Then m = .Cells(.Rows.Count, "A").End(xlUp).Row   1
    End With
    findId = CLng(m)
End Function

But can't get this to find a match. Should it matter that Match(ids, is located on a different sheet to .Columns("A")?

CodePudding user response：

Try something like this:

Function findId(id As String) As Long
    Dim m
    With Sheets("Data")
        m = Application.Match(id, .Columns("A"), 0) 'any match on id?
        'got a match?  If not get next empty cell
        If IsError(m) Then m = .Cells(.Rows.Count, "A").End(xlUp).Row   1
    End With
    findId = CLng(m)
End Function

FYI there's no reason in VBA to use Integer over Long, and using longs is safer when dealing with ranges, since integer will overflow above approx. 32,000