vba search for word and go to cell

[ste33uka]

Hi could someone give me a vba that would bring up a pop up box where i would type, it would then search column "A"
for the word typed from the current row +1 downwards, once word is found , it would go to cell and show popup box that word is found,
lastly once it had searched column "a" to last row it would bring up pop up box saying " complete"
Thank

 

[Legacy 143009]

Hi,

this will work for you:

Sub test()
  Dim searchString As String, rowNumber As Long
  searchString = InputBox("Enter the word")
  With Application
  If Not IsError(.Match(searchString, Range("A:A"), 0)) Then
    rowNumber = .Match(searchString, Range("A:A"), 0)
    Range("A" & rowNumber).Select
    MsgBox "Word is found on row " & rowNumber
  End If
  MsgBox "Search Complete!"
  End With
End Sub

 

[ste33uka]

thanks but once it finds example "red" on a22, it then refinds "red" on a22, as it need to search from current line plus 1

 

[Legacy 143009]

Do you have more than one instances of the same word?

 

[ste33uka]

Do you have more than one instances of the same word?

yes there could be 30

 

[Legacy 143009]

Then:

Sub test()
  Dim searchString As String, rowNumber As Long, i As Long, lRow As Long
  lRow = Cells(Rows.Count, "A").End(xlUp).Row
  searchString = InputBox("Enter the word")
  With Application
  For i = 1 To lRow
    If Not IsError(.Match(searchString, Range("A" & i  &  ":A" & lRow), 0)) Then
      rowNumber = .Match(searchString, Range("A" & i  &  ":A" & lRow), 0)
      Range("A" & rowNumber).Select
      MsgBox "Word is found on row " & rowNumber
      i = rowNumber
    End If
  Next
  MsgBox "Search Complete!"
  End With
End Sub

 

[ste33uka]

finds word , but then popup box when press ok reappears over and over again, had to kill excel

 

[Legacy 143009]

Ok my mistake. I edited the code. Please try again

 

[ste33uka]

same thing happens

 

[Legacy 143009]

What do you want?
Do you want to stop on that row, do something in excel, then continue where you left?

VBA doesn't work like that. Once you run, you have to wait until it finishes.

If this is what you want one solution can be to keep the track of where you left last time. You should reserve a cell for this. First put value 1 to B1 cell.

Sub test()
  Dim searchString As String, rowNumber As Long, lRow As Long, lastValue As Long
  lRow = Cells(Rows.Count, "A").End(xlUp).Row
  searchString = InputBox("Enter the word")
  lastValue = Range("B1").Value
  With Application
    If Not IsError(.Match(searchString, Range("A" & lastValue  &  ":A" & lRow), 0)) Then
      rowNumber = .Match(searchString, Range("A" & lastValue &  ":A" & lRow), 0)
      Range("A" & rowNumber).Select
      MsgBox "Word is found on row " & rowNumber
      Range("B1").Value = rowNumber + 1
    Else
      MsgBox "Search Complete!"
    End If
  End With
End Sub