Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching a worksheet from userform

    Does anyone have any insight as to how I can search a worksheet for a userform, recognize the row the item is in, and set a variable in code to resemble that row?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    search a worksheet FROM a userform... [img]/w3timages/icons/doh.gif[/img]
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    Try something like this:

    Dim rngFind As Range
    Dim intRownum As Integer
    Set rngFind = Sheet1.Range("a1:g20").Find(UserForm1.TextBox1)
    intRownum = test.Row

    intrownum will contain the row number of the found cell.

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    Groovy..

    How would I incorporate this into the following?:

    '*** CODE START ***
    Sub btnfind_click()
    Cells.Find(What:="Prototype", After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
    End Sub


    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    Dim rngFind As Range
    Dim intRownum As Integer
    Set rngFind = Sheet1.UsedRange.Find(What:="Prototype", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    rngFind.Select
    End Sub


    You said you wanted to seach from a form, in which case just change "prototype" to reference the text box on your form.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    Is there any way to replace "prototype" with a value entered by the user?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    Yes, as I said just replace "prototype" with a reference to a text box on your user form:

    Dim rngFind As Range
    Dim intRownum As Integer
    Set rngFind = Sheet1.UsedRange.Find(What:=userform1.textbox1, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    rngFind.Select
    End Sub

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    my code is returning an error message if the search string does not exist... any ideas?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    It doesn't like the intrownum either... please help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    try this:

    Dim rngFind As Range
    if isnull(userform1.textbox1) then exit sub
    Set rngFind = Sheet1.UsedRange.Find(What:=userform1.textbox1, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    rngFind.Select

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    Perhaps I should explain better.

    There is a value in my form code, called i. i represents the row that the form is currently reflecting. Because my users are completely unfamiliar with Excel, I am using a userform to relay information to the worksheet.

    Ideally, they would be able to search the worksheet based on whatever they have typed in frmtrack.combobox1.

    If I use the rngfind.select, it has no effect on the form, I instead need to assign a variable reflecting the row number to i, then call a 'getvaluefromworksheet' sub.

    Does that make any sense? )

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    [img]/w3timages/icons/thinks.gif[/img]And...

    Based on that "i" value, can you think of any way to add a goto-first/goto-last button to the form?

    Thanks Millions!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  13. #13
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    You can assign the row value of the found cell:

    i=row(rngFind)

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    VBA doesn't like that...

    referencing the following line:
    i = ROW(rngFind)
    ROW is highlighted...

    error:
    Sub or function not defined.

    Any ideas? )

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  15. #15
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching a worksheet from userform

    try:

    i = rngfind.row

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •