| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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.... |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculations did not find required folder | Excel Discussion (Misc queries) | |||
| Duplicate max values in one cell required | Excel Discussion (Misc queries) | |||
| Excel 2002: How to find only the required data in a list ? | Excel Discussion (Misc queries) | |||
| Password Required to View\edit a Sheet in a Workbook with Many She | Excel Discussion (Misc queries) | |||
| Required sum, need values to adapt with changes | Excel Programming | |||
Linear Mode
