Find And Select Multiple Cells

[YasserKhalil]

Hello every body

In Range("I2") I have avalue ..
I want to use vba to find this value in the range ("A1:H50") as a whole

I want the code to be in Worksheet_Change so as to when typing a new value in I2 the cells that match this value to be selected

 

[L. Howard]

Hi YasserKhalil,

Here is another to try.

Regards,
Howard


Put this in the sheet module.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'/ by[B] Garry MS Public Prog[/B].

If Intersect(Target, Range("I2")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then
MsgBox "Must be a number!"
Range("I2").Select
Exit Sub
End If
  If Target = Range("I2") Then FindMyVal Target.Value
End Sub

Put this in a Standard module.

Option Explicit

Sub FindMyVal(ValToFind&)
'/ by [B]Garry MS Public Prog.[/B]

Dim n&, k&, sz$, rng As Range

  n = WorksheetFunction.CountIf(ActiveSheet.Range("A1:H50"), ValToFind)
  If n = 0 Then Exit Sub

  With ActiveSheet.Range("A1:H50")
    If n = .Cells.Count Then .Select: Exit Sub

    For n = 1 To .Cells.Count

      If .Cells(n) = ValToFind And rng Is Nothing Then
        Set rng = .Cells(n)
      ElseIf .Cells(n) = ValToFind And Not rng Is Nothing Then
        Set rng = Union(rng, .Cells(n))
      End If
    Next 'n
  End With 'ActiveSheet.Range("A1:H50")
  rng.Select
End Sub

 

[YasserKhalil]

Amazing Mr. Howard
Thank you very much for your great solution

 

[L. Howard]

You're welcome.

Please note that I am not the author of these codes.

See the names under the sub titles.

Regards,
Howard