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")
?
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