CODEWITHSUNDEEP
vbaexcel
Pvelez_3
Pvelez_3

Reputation: 115

Finding next blank row after the user inputs a specific prefix

I have a set amount of tag numbers in column A. The prefix to these tag numbers vary i.e "35C-1243" or "35TC-1234". Each prefix comes with a lot of tag numbers however there are some blank rows in between tag numbers for skipped tags for example it could go from "35C-1234" to "35C-1235" to "35C-1237". This would mean that I have a blank row in between "35C-1235" and "35C-1237". What I have been trying to do is create a code that would prompt the user to enter the prefix for the tag number they want to focus on and based off that prefix the next blank row should be selected however I can't get the excel to activate the blank row. Any suggestions/help will be greatly appreciated.

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

Select Case x

    Case x = "35C"
    NextFree = Range("A:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
    Range("A" & NextFree).Select

    End Select
Else
Cancel = True

End If



End Sub

Upvotes: 0

Views: 28

Answers (1)

Egan Wolf
Egan Wolf

Reputation: 3573

The problem is in your Select Case. It should be Case "35C" instead of Case x = "35C". What you do now is compare x to the result of x = "35C", which is True, but x is not equal True, so this case is False :)

Also what @FreeMan said about Range("A:A" & Rows.Count) which should be Range("A:A").

Upvotes: 1

Related Questions