How can i find and View required values ?
 

I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

How can i find and View required values ?
 

I would drop the Find and use data|filter|autofilter.

Then I could use the arrow to do a custom filter to show the rows that have
values = whatever number I wanted.

Corey wrote:

I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

--

Dave Peterson

How can i find and View required values ?
 

find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

How can i find and View required values ?
 

Tom,
Trying your code i am getting a Type Mismatch error he

If v(i, 1) < False Then


I do not really understand what this bit does so i cannot solve it my self, yet?
What if there is More than 1 value found, is this displayed on a msgbox??

Corey....


"Tom Ogilvy" wrote in message ...
find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

How can i find and View required values ?
 

That would happen if you error values in column C. This will work around
that:

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If Not IsError(v(i, 1)) Then
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
End If
Next
MsgBox s
End Sub

Yes, it shows multiple values. I wouldn't see using a msgbox as the final
product, but knowing nothing else about what you are doing, it is a good
placeholder.

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
Tom,
Trying your code i am getting a Type Mismatch error he

If v(i, 1) < False Then


I do not really understand what this bit does so i cannot solve it my
self, yet?
What if there is More than 1 value found, is this displayed on a msgbox??

Corey....


"Tom Ogilvy" wrote in message
...
find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should
you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

How can i find and View required values ?
 

Tom,
I still get the same error???
"Tom Ogilvy" wrote in message ...
That would happen if you error values in column C. This will work around
that:

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If Not IsError(v(i, 1)) Then
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
End If
Next
MsgBox s
End Sub

Yes, it shows multiple values. I wouldn't see using a msgbox as the final
product, but knowing nothing else about what you are doing, it is a good
placeholder.

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
Tom,
Trying your code i am getting a Type Mismatch error he

If v(i, 1) < False Then


I do not really understand what this bit does so i cannot solve it my
self, yet?
What if there is More than 1 value found, is this displayed on a msgbox??

Corey....


"Tom Ogilvy" wrote in message
...
find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should
you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

How can i find and View required values ?
 

In this line:

If Not IsError(v(i, 1)) Then

Corey...
"Tom Ogilvy" wrote in message ...
That would happen if you error values in column C. This will work around
that:

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If Not IsError(v(i, 1)) Then
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
End If
Next
MsgBox s
End Sub

Yes, it shows multiple values. I wouldn't see using a msgbox as the final
product, but knowing nothing else about what you are doing, it is a good
placeholder.

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
Tom,
Trying your code i am getting a Type Mismatch error he

If v(i, 1) < False Then


I do not really understand what this bit does so i cannot solve it my
self, yet?
What if there is More than 1 value found, is this displayed on a msgbox??

Corey....


"Tom Ogilvy" wrote in message
...
find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should
you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

How can i find and View required values ?
 

I've tested that version, with numbers, a single letter, text string,
blanks, errors, and booleans. It works fine for me. I can't think of
single value type that would cause Iserror to return a type mismatch error.

More likely you have changed the range and have not adjusted the i = 1 to
500 to match causing a subscript out of range error, but that is only a
guess.


If that is the error, then Maybe change it to

For i = lbound(v,1) To ubound(v,1)
--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
In this line:

If Not IsError(v(i, 1)) Then

Corey...
"Tom Ogilvy" wrote in message
...
That would happen if you error values in column C. This will work around
that:

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If Not IsError(v(i, 1)) Then
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
End If
Next
MsgBox s
End Sub

Yes, it shows multiple values. I wouldn't see using a msgbox as the final
product, but knowing nothing else about what you are doing, it is a good
placeholder.

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
Tom,
Trying your code i am getting a Type Mismatch error he

If v(i, 1) < False Then


I do not really understand what this bit does so i cannot solve it my
self, yet?
What if there is More than 1 value found, is this displayed on a msgbox??

Corey....


"Tom Ogilvy" wrote in message
...
find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should
you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

How can i find and View required values ?
 

I placed the EXCACT code in a Module and run it from a Forms Button.
Placed a value say "10" in Sheet1.Range("G8").

There is Numersous Text and Numerical values throughout Sheet4 Column C some and Some < "10"

Corey....
"Tom Ogilvy" wrote in message ...
I've tested that version, with numbers, a single letter, text string,
blanks, errors, and booleans. It works fine for me. I can't think of
single value type that would cause Iserror to return a type mismatch error.

More likely you have changed the range and have not adjusted the i = 1 to
500 to match causing a subscript out of range error, but that is only a
guess.


If that is the error, then Maybe change it to

For i = lbound(v,1) To ubound(v,1)
--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
In this line:

If Not IsError(v(i, 1)) Then

Corey...
"Tom Ogilvy" wrote in message
...
That would happen if you error values in column C. This will work around
that:

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If Not IsError(v(i, 1)) Then
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
End If
Next
MsgBox s
End Sub

Yes, it shows multiple values. I wouldn't see using a msgbox as the final
product, but knowing nothing else about what you are doing, it is a good
placeholder.

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
Tom,
Trying your code i am getting a Type Mismatch error he

If v(i, 1) < False Then


I do not really understand what this bit does so i cannot solve it my
self, yet?
What if there is More than 1 value found, is this displayed on a msgbox??

Corey....


"Tom Ogilvy" wrote in message
...
find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should
you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....

How can i find and View required values ?
 

In Tom's code, this line:

v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")

Is using the names of the worksheets you see on the tab--not the codename of the
worksheet.

Remember to enclose the worksheet name in apostrophes if required:

v = Evaluate("if('Sheet 4'!$C$1:$C$500'Sheet 1'!$G$8," & _
"'Sheet 4'!$C$1:$C$500)")



Corey wrote:

I placed the EXCACT code in a Module and run it from a Forms Button.
Placed a value say "10" in Sheet1.Range("G8").

There is Numersous Text and Numerical values throughout Sheet4 Column C some and Some < "10"

Corey....
"Tom Ogilvy" wrote in message ...
I've tested that version, with numbers, a single letter, text string,
blanks, errors, and booleans. It works fine for me. I can't think of
single value type that would cause Iserror to return a type mismatch error.

More likely you have changed the range and have not adjusted the i = 1 to
500 to match causing a subscript out of range error, but that is only a
guess.

If that is the error, then Maybe change it to

For i = lbound(v,1) To ubound(v,1)
--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
In this line:

If Not IsError(v(i, 1)) Then

Corey...
"Tom Ogilvy" wrote in message
...
That would happen if you error values in column C. This will work around
that:

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If Not IsError(v(i, 1)) Then
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
End If
Next
MsgBox s
End Sub

Yes, it shows multiple values. I wouldn't see using a msgbox as the final
product, but knowing nothing else about what you are doing, it is a good
placeholder.

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
Tom,
Trying your code i am getting a Type Mismatch error he

If v(i, 1) < False Then


I do not really understand what this bit does so i cannot solve it my
self, yet?
What if there is More than 1 value found, is this displayed on a msgbox??

Corey....


"Tom Ogilvy" wrote in message
...
find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should
you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....









--

Dave Peterson

How can i find and View required values ?
 

Thank you dave.
That was the error i was getting.
Changed the code to the Actual Sheet names am i get a list of values.
Now i just need to solve how to view them better.

Corey....
"Dave Peterson" wrote in message
...
In Tom's code, this line:

v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")

Is using the names of the worksheets you see on the tab--not the codename of the
worksheet.

Remember to enclose the worksheet name in apostrophes if required:

v = Evaluate("if('Sheet 4'!$C$1:$C$500'Sheet 1'!$G$8," & _
"'Sheet 4'!$C$1:$C$500)")



Corey wrote:

I placed the EXCACT code in a Module and run it from a Forms Button.
Placed a value say "10" in Sheet1.Range("G8").

There is Numersous Text and Numerical values throughout Sheet4 Column C some and Some < "10"

Corey....
"Tom Ogilvy" wrote in message ...
I've tested that version, with numbers, a single letter, text string,
blanks, errors, and booleans. It works fine for me. I can't think of
single value type that would cause Iserror to return a type mismatch error.

More likely you have changed the range and have not adjusted the i = 1 to
500 to match causing a subscript out of range error, but that is only a
guess.

If that is the error, then Maybe change it to

For i = lbound(v,1) To ubound(v,1)
--
Regards,
Tom Ogilvy

"Corey" wrote in message
...
In this line:

If Not IsError(v(i, 1)) Then

Corey...
"Tom Ogilvy" wrote in message
...
That would happen if you error values in column C. This will work around
that:

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If Not IsError(v(i, 1)) Then
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
End If
Next
MsgBox s
End Sub

Yes, it shows multiple values. I wouldn't see using a msgbox as the final
product, but knowing nothing else about what you are doing, it is a good
placeholder.

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
Tom,
Trying your code i am getting a Type Mismatch error he

If v(i, 1) < False Then


I do not really understand what this bit does so i cannot solve it my
self, yet?
What if there is More than 1 value found, is this displayed on a msgbox??

Corey....


"Tom Ogilvy" wrote in message
...
find doesn't have an option of finding values greater than another value.

One way would be

Sub ABC()
Dim v As Variant, i As Long
Dim s As String
v = Evaluate("if(Sheet4!$C$1:$C$500Sheet1!$G$8," & _
"Sheet4!$C$1:$C$500)")
For i = 1 To 500
If v(i, 1) < False Then
s = s & v(i, 1) & ","
End If
Next
MsgBox s
End Sub

This is an array formula, so you can't use an entire column (nor should
you
want to).

--
Regards,
Tom Ogilvy


"Corey" wrote in message
...
I have data in sheet4 that i need to search for by value.
I want the user to Place a Numerical value in sheet1.Range(G8)
Then Click the Forms Button to search through Column C in Sheet4 for
values (=) that value.

What would be the BEST way to FIND this value(Macro Recorder does not
Record a FIND??), and
HOW can i display the Values found effectively for the User to VIEW ?

Corey....









--

Dave Peterson