1

I am trying to select one cell to the left and to the right of a cell I have found by it's value. I am new at writing VBA macros and have hacked together the following script from a few different articles I found around the internet.

Sub myselect()

myCell = Application.InputBox("Find:")
Dim rngFound As Range

With Cells
  Set rngFound = .Find(What:=myCell, After:=.Cells(1, .Columns.Count), _
  LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, MatchCase:=False)
End With

If Not rngFound Is Nothing Then
  For Each c In rngFound.CurrentRegion.Cells
      Range(c.Offset(0, -1), c.Offset(0, 1)).Select
  Next
Else
  MsgBox "Search term not found!"
End If

End Sub

I know it is probably not written very well so I am open to any suggestions on how to clean it up. It does what I want it to now but it only does it for the first result it finds. How can I edit it to select the adjacent cells of each result it finds? I guessing I need to loop through the results but I tried that and it is not working. Thanks.

1

0

You must log in to answer this question.

Browse other questions tagged .