-1

I'm trying to find some macro that will run all over the worksheet and select all the relevant cells.

I have written some macro that find the cell but only one cell-its not selecting all the cells.

Dim myRange As Range
Dim myCell As Range
Set myRange = Range("A1:GG1000")
Dim mynumer As Integer
mynumber = 7
For Each myCell In myRange
If myCell = mynumber Then

myCell.Select


End If
Next myCell

how i can run the macro and see all the relevant cells? thanks!

Mikku
  • 6,453
  • 2
  • 14
  • 38
j-r23
  • 29
  • 1
  • 2

2 Answers2

0

Maybe try some .FindNext iteration.

Just adapted from the above link:

Sub Test()

Dim cl As Range, rng As Range
With ThisWorkbook.Sheets("Sheet1").Range("A1:GG1000")
    Set cl = .Find(7, LookIn:=xlValues, lookat:=xlWhole)
     If Not cl Is Nothing Then
        firstAddress = cl.Address
        Do
            If Not rng Is Nothing Then
                Set rng = Union(rng, cl)
            Else
                Set rng = cl
            End If
            Debug.Print rng.Address
            Set cl = .FindNext(cl)
        If cl Is Nothing Then
            GoTo DF
        End If
        Loop While cl.Address <> firstAddress
      End If
DF:
    rng.Select
End With

End Sub

The question really is, why do you .Select a range? Most of the time that can be avoided, and most likely the code above can be amended to something much cleaner!

JvdV
  • 53,146
  • 6
  • 36
  • 60
-1

Please take a look at this answer: How to find a value in an excel column by vba code Cells.Find

The answer beneath the top voted, shows you how to search in the whole spreadsheet.

Best regards,

Timo

T. Brüntjen
  • 9
  • 1
  • 2