Find and display rows with criteria
 

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.

Find and display rows with criteria
 

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.

Find and display rows with criteria
 

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.

Find and display rows with criteria
 

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.

Find and display rows with criteria
 

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.

Find and display rows with criteria
 

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

Find and display rows with criteria
 

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.

Find and display rows with criteria
 

Gary, i mean- to supercede the existing sumproduct formbox when you hit fx
button?

"Gary''s Student" wrote:

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.

Find and display rows with criteria
 

Gary,

Two things; I can't get the code to run through the loop and I see declared
variable R2 as Range. Was that supposed to be used in the code somewhere?

"driller" wrote:

Gary, i mean- to supercede the existing sumproduct formbox when you hit fx
button?

"Gary''s Student" wrote:

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.

Find and display rows with criteria
 

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

Find and display rows with criteria
 

Sorry, It does work right. My fault...thanks

"Billy B" wrote:

Gary,

Two things; I can't get the code to run through the loop and I see declared
variable R2 as Range. Was that supposed to be used in the code somewhere?

"driller" wrote:

Gary, i mean- to supercede the existing sumproduct formbox when you hit fx
button?

"Gary''s Student" wrote:

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.