Discounting the good advice that you seldom ever have to select cells to work with them...

How would you "select" all the cells on a sheet (.used range I suppose) of an input box entry.

The code equivalent of "ctrl key + click" on each cell with the number 5 in it.

The macro recorder gives you a bunch of cell address's, I want code to just select all the cells with 5 in them.

Thanks,
Howard

One way...

Sub FindMyVal()
Dim n&, k&, sz$

ReDim vArray(1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange,
5))
For n = LBound(vArray) To UBound(vArray)
With ActiveSheet.UsedRange
For k = 1 To .Cells.Count
If .Cells(k) = 5 And InStr(sz, .Cells(k).Address) = 0 Then
sz = sz & "," & .Cells(k).Address: Exit For
End If
Next 'k
End With 'ActiveSheet.UsedRange
Next 'n
' sz = Replace(Mid(sz, 2), ",", ", ")
Range(Replace(Mid(sz, 2), ",", ", ")).Select
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Sunday, October 26, 2014 8:17:57 PM UTC-7, GS wrote:
One way...

Sub FindMyVal()
Dim n&, k&, sz$

ReDim vArray(1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange,
5))
For n = LBound(vArray) To UBound(vArray)
With ActiveSheet.UsedRange
For k = 1 To .Cells.Count
If .Cells(k) = 5 And InStr(sz, .Cells(k).Address) = 0 Then
sz = sz & "," & .Cells(k).Address: Exit For
End If
Next 'k
End With 'ActiveSheet.UsedRange
Next 'n
' sz = Replace(Mid(sz, 2), ",", ", ")
Range(Replace(Mid(sz, 2), ",", ", ")).Select
End Sub

--
Garry

Thanks, Garry.

I thought this to be an easier task until I flailed about with hapless attempts.

The only thing I got correct in my mind as to how to do this was to read the desired cells (containing 5) into an array.

With your code:

If the UsedRange has 8 cells with the number 5 in them, we have an 8 element vArray.

Then for each cell in the UsedRange, the If statement must = 5 and return 0 from the InStr query for the cell address to be remembered in sz.

If .Cells(k) = 5 And InStr(sz, .Cells(k).Address) = 0 Then
sz = sz & "," & .Cells(k).Address: Exit For
End If

I don't get the InStr portion and sz.

But it sure does work.

Howard

Then for each cell in the UsedRange, the If statement must = 5 and
return 0 from the InStr query for the cell address to be remembered
in sz.

If .Cells(k) = 5 And InStr(sz, .Cells(k).Address) = 0 Then
sz = sz & "," & .Cells(k).Address: Exit For
End If

I don't get the InStr portion and sz.

This adds the cell address to sz if it's value matches the criteria,
only if the address is not already there. Var sz holds the addresses so
they can be formatted correctly to pass as a valid list for Range().

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

This adds the cell address to sz if it's value matches the criteria,
only if the address is not already there. Var sz holds the addresses so
they can be formatted correctly to pass as a valid list for Range().

--
Garry

Okay, makes some sense to me now. Thanks.

Here is a slightly modified version I intend to pass on unless there is already a solution provided. (Been chasing this for most of a day.)

Howard

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

Application.EnableEvents = False

If IsNumeric(Target.Value) = False Then
Range("F2").Select
MsgBox "Must be number!"
Range("F1").Activate
Application.EnableEvents = True
Exit Sub
End If

If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub

Dim aNum As Long
Dim n&, k&, sz$

aNum = Range("F1")

ReDim vArray(1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, aNum))
For n = LBound(vArray) To UBound(vArray)
With ActiveSheet.UsedRange
For k = 1 To .Cells.Count
If .Cells(k) = aNum And InStr(sz, .Cells(k).Address) = 0 Then
sz = sz & "," & .Cells(k).Address: Exit For
End If
Next 'k
End With 'ActiveSheet.UsedRange
Next 'n
'sz = Replace(Mid(sz, 2), ",", ", ")
Range(Replace(Mid(sz, 2), ",", ", ")).Select

Application.EnableEvents = True

End Sub

I meant to mention that I feel the error check for non numeric entry is a bit clunky.

Seems something has to be selected or a previous number will leave all the values of it selected on the sheet after a non number is entered.

Howard

Hi Howard,

Am Sun, 26 Oct 2014 14:38:35 -0700 (PDT) schrieb L. Howard:

How would you "select" all the cells on a sheet (.used range I suppose) of an input box entry.

another suggestion:

Sub MultiSelect()
Dim rngBig As Range, rngC As Range

For Each rngC In ActiveSheet.UsedRange
If rngC.Value = 5 And rngBig Is Nothing Then
Set rngBig = rngC
ElseIf rngC = 5 And Not rngBig Is Nothing Then
Set rngBig = Union(rngBig, rngC)
End If
Next
rngBig.Select

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi again,

Am Mon, 27 Oct 2014 08:21:59 +0100 schrieb Claus Busch:

another suggestion:

another suggestion:

Sub MultiSelect2()
Dim rngBig As Range, c As Range
Dim Firstaddress As String

With ActiveSheet.UsedRange
Set c = .Find(5, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Firstaddress = c.Address
Do
If rngBig Is Nothing Then
Set rngBig = c
Else
Set rngBig = Union(rngBig, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < Firstaddress
End If
End With
rngBig.Select
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Monday, October 27, 2014 12:30:31 AM UTC-7, Claus Busch wrote:
Hi again,

Am Mon, 27 Oct 2014 08:21:59 +0100 schrieb Claus Busch:

another suggestion:

another suggestion:

Sub MultiSelect2()
Dim rngBig As Range, c As Range
Dim Firstaddress As String

With ActiveSheet.UsedRange
Set c = .Find(5, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Firstaddress = c.Address
Do
If rngBig Is Nothing Then
Set rngBig = c
Else
Set rngBig = Union(rngBig, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < Firstaddress
End If
End With
rngBig.Select
End Sub


Regards
Claus B.
--


Thanks Claus.

Really have some good stuff to work with.

Regards,
Howard