-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,538
  • 3
  • 15
  • 38
j-r23
  • 39
  • 1
  • 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
  • 70,606
  • 8
  • 39
  • 70
-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