Hello folks,
im a VB novice but have taken on a project at work to put some VB code behind an excel spreadsheet. I hacked some code that I found on the web to try and do what I need.
I need the code to create a find box into which a search string is input. The code should find the string at a particular cell, then copies this cell and four cells to the right to a location in sheet 2 of the same workbook
The code runs but comes back with a runtime 424 error. I believe the issue is being caused by "nextCell" but I cant see why.
Any support would be appreciated
Sub FindStrings()
Dim firstCell, nextCell, stringToFind As String
' Show an input box and return the entry to a variable.
stringToFind = _
Application.InputBox("Enter J Number and Stage Number. For Example J1234 ST1", "Search String")
' Set an object variable to evaluate the Find command.
Set firstCell = Cells.Find(what:=stringToFind, lookat:=xlWhole, _
searchdirection:=xlPrevious)
' If the string is not found, show this message box.
If firstCell Is Nothing Then
MsgBox "Search Value Not Found.", vbExclamation
Else
' Otherwise, find the next occurrence of the search text.
nextCell = _
Cells.FindNext(after:=Range(firstCell.Address)).Address
' Show its address in a message box.
Range(nextCell, nextCell.Offset(0, 4)).Copy
Sheets("Sheet2").Select
Range("B3").Select
ActiveSheet.Paste
End If
End Sub
im a VB novice but have taken on a project at work to put some VB code behind an excel spreadsheet. I hacked some code that I found on the web to try and do what I need.
I need the code to create a find box into which a search string is input. The code should find the string at a particular cell, then copies this cell and four cells to the right to a location in sheet 2 of the same workbook
The code runs but comes back with a runtime 424 error. I believe the issue is being caused by "nextCell" but I cant see why.
Any support would be appreciated
Sub FindStrings()
Dim firstCell, nextCell, stringToFind As String
' Show an input box and return the entry to a variable.
stringToFind = _
Application.InputBox("Enter J Number and Stage Number. For Example J1234 ST1", "Search String")
' Set an object variable to evaluate the Find command.
Set firstCell = Cells.Find(what:=stringToFind, lookat:=xlWhole, _
searchdirection:=xlPrevious)
' If the string is not found, show this message box.
If firstCell Is Nothing Then
MsgBox "Search Value Not Found.", vbExclamation
Else
' Otherwise, find the next occurrence of the search text.
nextCell = _
Cells.FindNext(after:=Range(firstCell.Address)).Address
' Show its address in a message box.
Range(nextCell, nextCell.Offset(0, 4)).Copy
Sheets("Sheet2").Select
Range("B3").Select
ActiveSheet.Paste
End If
End Sub