Using the find method to find next available blank row

Viewed 503

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?

enter image description here

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.

2 Answers
Related