Using the find method to find next available blank row

Question

Column A has tag numbers that vary based on the prefix to that tag number. for example:

• 35C-1234
• blank row here
• 35C-1236
• 35C-1237
• 35TC-1234
• 35TC-1235
• Blank row here
• 35TC-1237

what I am looking to have the macro do is find the first occurrence of a blank row after the user inputs the tag number prefix. In this case, if the user were wanting to find a blank row for the tag number prefix of "35TC" then the blank row after tag number "35TC-1235" would be selected and not the blank row after tag number "35C-1234". I got the code to find a blank row however, I am having trouble implementing the .Find() function into this code and would greatly appreciate any help on this!

Code being used:

Private Sub Worksheet_Activate()    
    Dim msg As String
    Dim result As Integer
    Dim x As String
    msg = "Would you like to find the next available tag number?"
    result = MsgBox(msg, vbYesNo)

    If result = vbYes Then
        x = Application.InputBox("Enter the part reference ")
'need to work on the find function here
        Cells.Find(What:=c, Lookin:=range("A"))

        NextFree = Range("A:A").Cells.SpecialCells(xlCellTypeBlanks).Row
        Range("A" & NextFree).Select
    Else
        Cancel = True       
    End If
End Sub

Edit to question:

Through the help of the two answers below, I have been able to get the space after the prefix has been declared by the user. I now am running into two problems, where 1) If the declared prefix does not have a blank row, the code will jump to the next available blank row even if it has a different prefix. Instead of that happening i would like for it to maybe go to the very last tag containing the prefix and output a message declaring that the prefix does not have any blank rows. 2) I also just added a new prefix "35CA". Now when I go to search "35C" the prefix of "35CA" is included in that. How do I keep it to just give me results pertaining to exactly what I searched for?

In this case, if i searched for "35C" the code will then jump down to the blank after "35CA-1600-K02". This is an example where both problems explained above occur.

Answer 1

Another way it this...

Sub FindBlankRow()
Dim Rng As Range, eRng As Range
Dim lr As Long
Dim str As String
str = InputBox("Enter the part reference ")
lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
On Error Resume Next
Set Rng = Range("A2:A" & lr).SpecialCells(xlCellTypeBlanks)
If Not Rng Is Nothing Then
    For Each eRng In Rng.Areas
        If InStr(LCase(eRng.Cells(1).Offset(-1, 0)), LCase(str)) > 0 Then
            eRng.Cells(1).Select
            Exit For
        End If
    Next eRng
End If
End Sub

Edited Answer:

Sub FindBlankRow()
Dim Cell As Range
Dim str As String, firstcell As String
str = InputBox("Enter the part reference ")
If str = "" Then Exit Sub
If Right(str, 1) <> "-" Then str = str & "-"    
With Range("A:A")
    Set Cell = .Find(str, lookat:=xlPart, MatchCase:=False)
    If Not Cell Is Nothing Then
        firstcell = Cell.Address
        Do
            If Cell.Offset(1, 0) = "" Then
                Cell.Select
                Exit Sub
            ElseIf InStr(LCase(Cell.Offset(1, 0)), LCase(str)) = 0 Then
                Cell.Select
                MsgBox "No blank cell was found below a code with prefix " & str & ".", vbExclamation
                Exit Sub
            End If
            Set Cell = .FindNext(Cell)
        Loop While Not Cell Is Nothing And firstcell <> Cell.Address
    End If
End With
End Sub

Answer 2

First, you're looking for c, which you never define/declare. Also, you probably want to use a Range variable to store the next non-empty cell.

Try this:

Sub find_value()
Dim msg As String, lookForValue As String
Dim result As Integer
Dim foundCell As Range, emptyCell As Range

msg = "Would you like to find the next available tag number?"
result = MsgBox(msg, vbYesNo)

If result = vbYes Then
    lookForValue = Application.InputBox("Enter the part reference ")
    'need to work on the find function here
    Set foundCell = Range("A:A").Find(What:=lookForValue & "*")
    Debug.Print foundCell.Address
    Set emptyCell = foundCell.End(xlDown).Offset(1, 0)
    ' Now you have your cell. Do whatever with it...
    MsgBox ("The next empty cell is " & emptyCell.Address)
Else
    Cancel = True
End If

End Sub

Note: For some reason, it's not using $A$2 for the first blank cell if you search for 35C, but let me work on that. It may help if you could post a little more data and examples, to add some "error" handling.