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.
