I need some help writing a macro that would prompt the user for a value,
search the active worksheet and display all records that contain the value
input in the inputbox.

Thank you for your help.

Sub billy()
Dim r As Range
v = Application.InputBox("Enter value: ", Type:=1)

For Each r In ActiveSheet.UsedRange
If r.Value = v Then
MsgBox (r.Address)
End If
Next
End Sub

The user enters a number and the sub displays all the records (addresses)
that contain that number.
--
Gary''s Student


"Billy B" wrote:

I need some help writing a macro that would prompt the user for a value,
search the active worksheet and display all records that contain the value
input in the inputbox.

Thank you for your help.

I was really looking to do something more like a filter. If I entered IT32 in
the inputbox, display the entire row if any cell in that row contains IT32.
Thanks again.

"Gary''s Student" wrote:

Sub billy()
Dim r As Range
v = Application.InputBox("Enter value: ", Type:=1)

For Each r In ActiveSheet.UsedRange
If r.Value = v Then
MsgBox (r.Address)
End If
Next
End Sub

The user enters a number and the sub displays all the records (addresses)
that contain that number.
--
Gary''s Student


"Billy B" wrote:

I need some help writing a macro that would prompt the user for a value,
search the active worksheet and display all records that contain the value
input in the inputbox.

Thank you for your help.

O.K. then:

1. this version looks for text
2. this version displays entire rows
3. this version will find the text even if it is embedded

Sub billy2()
Dim r As Range, r2 As Range
v = Application.InputBox("Enter value: ", Type:=2)
Rows("1:65536").EntireRow.Hidden = True

For Each r In ActiveSheet.UsedRange
If InStr(r.Value, v) < 0 Then
Rows(r.Row).EntireRow.Hidden = False
End If
Next
Cells(1, 1).Select
End Sub
--
Gary''s Student


"Billy B" wrote:

I was really looking to do something more like a filter. If I entered IT32 in
the inputbox, display the entire row if any cell in that row contains IT32.
Thanks again.

"Gary''s Student" wrote:

Sub billy()
Dim r As Range
v = Application.InputBox("Enter value: ", Type:=1)

For Each r In ActiveSheet.UsedRange
If r.Value = v Then
MsgBox (r.Address)
End If
Next
End Sub

The user enters a number and the sub displays all the records (addresses)
that contain that number.
--
Gary''s Student


"Billy B" wrote:

I need some help writing a macro that would prompt the user for a value,
search the active worksheet and display all records that contain the value
input in the inputbox.

Thank you for your help.

H Gary,
Can you do an input box for sumproduct....please !

"Gary''s Student" wrote:

O.K. then:

1. this version looks for text
2. this version displays entire rows
3. this version will find the text even if it is embedded

Sub billy2()
Dim r As Range, r2 As Range
v = Application.InputBox("Enter value: ", Type:=2)
Rows("1:65536").EntireRow.Hidden = True

For Each r In ActiveSheet.UsedRange
If InStr(r.Value, v) < 0 Then
Rows(r.Row).EntireRow.Hidden = False
End If
Next
Cells(1, 1).Select
End Sub
--
Gary''s Student


"Billy B" wrote:

I was really looking to do something more like a filter. If I entered IT32 in
the inputbox, display the entire row if any cell in that row contains IT32.
Thanks again.

"Gary''s Student" wrote:

Sub billy()
Dim r As Range
v = Application.InputBox("Enter value: ", Type:=1)

For Each r In ActiveSheet.UsedRange
If r.Value = v Then
MsgBox (r.Address)
End If
Next
End Sub

The user enters a number and the sub displays all the records (addresses)
that contain that number.
--
Gary''s Student


"Billy B" wrote:

I need some help writing a macro that would prompt the user for a value,
search the active worksheet and display all records that contain the value
input in the inputbox.

Thank you for your help.

Yes.

You can use an input box to define ranges with the mouse and then take the
ranges to form an equation.
--
Gary's Student


"driller" wrote:

H Gary,
Can you do an input box for sumproduct....please !

"Gary''s Student" wrote:

O.K. then:

1. this version looks for text
2. this version displays entire rows
3. this version will find the text even if it is embedded

Sub billy2()
Dim r As Range, r2 As Range
v = Application.InputBox("Enter value: ", Type:=2)
Rows("1:65536").EntireRow.Hidden = True

For Each r In ActiveSheet.UsedRange
If InStr(r.Value, v) < 0 Then
Rows(r.Row).EntireRow.Hidden = False
End If
Next
Cells(1, 1).Select
End Sub
--
Gary''s Student


"Billy B" wrote:

I was really looking to do something more like a filter. If I entered IT32 in
the inputbox, display the entire row if any cell in that row contains IT32.
Thanks again.

"Gary''s Student" wrote:

Sub billy()
Dim r As Range
v = Application.InputBox("Enter value: ", Type:=1)

For Each r In ActiveSheet.UsedRange
If r.Value = v Then
MsgBox (r.Address)
End If
Next
End Sub

The user enters a number and the sub displays all the records (addresses)
that contain that number.
--
Gary''s Student


"Billy B" wrote:

I need some help writing a macro that would prompt the user for a value,
search the active worksheet and display all records that contain the value
input in the inputbox.

Thank you for your help.

Billy B wrote:
I was really looking to do something more like a filter. If I entered IT32 in
the inputbox, display the entire row if any cell in that row contains IT32.
Thanks again.

"Gary''s Student" wrote:

Sub billy()
Dim r As Range
v = Application.InputBox("Enter value: ", Type:=1)

For Each r In ActiveSheet.UsedRange
If r.Value = v Then
MsgBox (r.Address)
End If
Next
End Sub

The user enters a number and the sub displays all the records (addresses)
that contain that number.
--
Gary''s Student


"Billy B" wrote:

I need some help writing a macro that would prompt the user for a value,
search the active worksheet and display all records that contain the value
input in the inputbox.

Thank you for your help.


my fav to show/hide data based on a criteria is the following sub: you
can modify it to take criteria from a msg box.

you set a range rng sized (rows, cols), in the below sub, criteria is a
formula in column 2.

Sub ShowHide()
Dim rng As Range
With ActiveSheet
Set rng = .Range(.Range("A11"), .Range("A11").End(xlDown))
End With
Set rng = rng.Resize(, 2)
Dim CBX As CheckBox
With ActiveSheet
Set CBX = .CheckBoxes(Application.Caller)
If CBX.Value = xlOn Then
rng.AutoFilter Field:=2, Criteria1:="1"
Else
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
.AutoFilterMode = False
End If
End If
End With
End Sub

cheers,
deano

I am using Excel 2000 and I think I need to add a reference to make this work
but which one is it?

Thanks

"deano" wrote:


Billy B wrote:
I was really looking to do something more like a filter. If I entered IT32 in
the inputbox, display the entire row if any cell in that row contains IT32.
Thanks again.

"Gary''s Student" wrote:

Sub billy()
Dim r As Range
v = Application.InputBox("Enter value: ", Type:=1)

For Each r In ActiveSheet.UsedRange
If r.Value = v Then
MsgBox (r.Address)
End If
Next
End Sub

The user enters a number and the sub displays all the records (addresses)
that contain that number.
--
Gary''s Student


"Billy B" wrote:

I need some help writing a macro that would prompt the user for a value,
search the active worksheet and display all records that contain the value
input in the inputbox.

Thank you for your help.


my fav to show/hide data based on a criteria is the following sub: you
can modify it to take criteria from a msg box.

you set a range rng sized (rows, cols), in the below sub, criteria is a
formula in column 2.

Sub ShowHide()
Dim rng As Range
With ActiveSheet
Set rng = .Range(.Range("A11"), .Range("A11").End(xlDown))
End With
Set rng = rng.Resize(, 2)
Dim CBX As CheckBox
With ActiveSheet
Set CBX = .CheckBoxes(Application.Caller)
If CBX.Value = xlOn Then
rng.AutoFilter Field:=2, Criteria1:="1"
Else
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
.AutoFilterMode = False
End If
End If
End With
End Sub

cheers,
deano