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....

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

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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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....
>
>

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

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" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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....
>
>

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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Tom,
> Trying your code i am getting a Type Mismatch error here:
>
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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....
>>
>>
>
>
>

Tom,
I still get the same error???
"Tom Ogilvy" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Tom,
> Trying your code i am getting a Type Mismatch error here:
>
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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....
>>
>>
>
>
>

In this line:

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

Corey...
"Tom Ogilvy" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Tom,
> Trying your code i am getting a Type Mismatch error here:
>
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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....
>>
>>
>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In this line:
>
> If Not IsError(v(i, 1)) Then
>
> Corey...
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Tom,
>> Trying your code i am getting a Type Mismatch error here:
>>
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>>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....
>>>
>>>
>>
>>
>>
>
>
>

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" <(E-Mail Removed)> wrote in message news:e2hd3%(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In this line:
>
> If Not IsError(v(i, 1)) Then
>
> Corey...
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Tom,
>> Trying your code i am getting a Type Mismatch error here:
>>
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>>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....
>>>
>>>
>>
>>
>>
>
>
>

In Tom's code, this line:

v = Evaluate("if(Sheet4!$C$1:$C$500>Sheet1!$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" <(E-Mail Removed)> wrote in message news:e2hd3%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > In this line:
> >
> > If Not IsError(v(i, 1)) Then
> >
> > Corey...
> > "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Tom,
> >> Trying your code i am getting a Type Mismatch error here:
> >>
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >> 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$500>Sheet1!$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" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >>>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