strFind not working as I wanted it to be

M

madhatter73

New Member
Joined
Mar 20, 2006
Messages
26
I've been trying out this code from Roy Cox's sample. Problem is it does not generate search result despite keying in the right keyword. Could anyone advise me on this, please? Apparently I still do not understand what strFind could really do....

I wanted to use the code to enable the user to find entered data so that the userform would show all 131 texboxt values. These had originally been transferred to Sheet2.

Thanks

Code: 
Private Sub Find_Click()
    Dim strFind, FirstAddress As String   'what to find
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet2.Range("A2", "A65536").End(xlUp)
    strFind = Own2.Value    'what to look for
    Dim f As Integer
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .Ctr2.Value = c.Offset(0, 1).Value
                .BgtCd2.Value = c.Offset(0, 2).Value
                .AcType2.Value = c.Offset(0, 3).Value
                .PrjCd2.Value = c.Offset(0, 4).Value
                .SubCd2.Value = c.Offset(0, 5).Value
                .Desc2.Value = c.Offset(0, 6).Value
                .PrjObj2.Value = c.Offset(0, 7).Value
                .KPI2.Value = c.Offset(0, 8).Value
                .StkHld2.Value = c.Offset(0, 9).Value
                .TgtNo2.Value = c.Offset(0, 10).Value
                .AvgCst2.Value = c.Offset(0, 11).Value
                .Jan1.Value = c.Offset(0, 12).Value
                .Feb1.Value = c.Offset(0, 13).Value
                .Mar1.Value = c.Offset(0, 14).Value
                .Apr1.Value = c.Offset(0, 15).Value
                .May1.Value = c.Offset(0, 16).Value
                .Jun1.Value = c.Offset(0, 17).Value
                .Jul1.Value = c.Offset(0, 18).Value
                .Aug1.Value = c.Offset(0, 19).Value
                .Sep1.Value = c.Offset(0, 20).Value
                .Oct1.Value = c.Offset(0, 21).Value
                .Nov1.Value = c.Offset(0, 22).Value
                .Dec1.Value = c.Offset(0, 23).Value
                .Jan2.Value = c.Offset(0, 24).Value
                .Feb2.Value = c.Offset(0, 25).Value
                .Mar2.Value = c.Offset(0, 26).Value
                .Apr2.Value = c.Offset(0, 27).Value
                .May2.Value = c.Offset(0, 28).Value
                .Jun2.Value = c.Offset(0, 29).Value
                .Jul2.Value = c.Offset(0, 30).Value
                .Aug2.Value = c.Offset(0, 31).Value
                .Sep2.Value = c.Offset(0, 32).Value
                .Oct2.Value = c.Offset(0, 33).Value
                .Nov2.Value = c.Offset(0, 34).Value
                .Dec2.Value = c.Offset(0, 35).Value
                .Jan3.Value = c.Offset(0, 36).Value
                .Feb3.Value = c.Offset(0, 37).Value
                .Mar3.Value = c.Offset(0, 38).Value
                .Apr3.Value = c.Offset(0, 39).Value
                .May3.Value = c.Offset(0, 40).Value
                .Jun3.Value = c.Offset(0, 41).Value
                .Jul3.Value = c.Offset(0, 42).Value
                .Aug3.Value = c.Offset(0, 43).Value
                .Sep3.Value = c.Offset(0, 44).Value
                .Oct3.Value = c.Offset(0, 45).Value
                .Nov3.Value = c.Offset(0, 46).Value
                .Dec3.Value = c.Offset(0, 47).Value
                .Jan4.Value = c.Offset(0, 48).Value
                .Feb4.Value = c.Offset(0, 49).Value
                .Mar4.Value = c.Offset(0, 50).Value
                .Apr4.Value = c.Offset(0, 51).Value
                .May4.Value = c.Offset(0, 52).Value
                .Jun4.Value = c.Offset(0, 53).Value
                .Jul4.Value = c.Offset(0, 54).Value
                .Aug4.Value = c.Offset(0, 55).Value
                .Sep4.Value = c.Offset(0, 56).Value
                .Oct4.Value = c.Offset(0, 57).Value
                .Nov4.Value = c.Offset(0, 58).Value
                .Dec4.Value = c.Offset(0, 59).Value
                .Jan5.Value = c.Offset(0, 60).Value
                .Feb5.Value = c.Offset(0, 61).Value
                .Mar5.Value = c.Offset(0, 62).Value
                .Apr5.Value = c.Offset(0, 63).Value
                .May5.Value = c.Offset(0, 64).Value
                .Jun5.Value = c.Offset(0, 65).Value
                .Jul5.Value = c.Offset(0, 66).Value
                .Aug5.Value = c.Offset(0, 67).Value
                .Sep5.Value = c.Offset(0, 68).Value
                .Oct5.Value = c.Offset(0, 69).Value
                .Nov5.Value = c.Offset(0, 70).Value
                .Dec5.Value = c.Offset(0, 71).Value
                .Jan6.Value = c.Offset(0, 72).Value
                .Feb6.Value = c.Offset(0, 73).Value
                .Mar6.Value = c.Offset(0, 74).Value
                .Apr6.Value = c.Offset(0, 75).Value
                .May6.Value = c.Offset(0, 76).Value
                .Jun6.Value = c.Offset(0, 77).Value
                .Jul6.Value = c.Offset(0, 78).Value
                .Aug6.Value = c.Offset(0, 79).Value
                .Sep6.Value = c.Offset(0, 80).Value
                .Oct6.Value = c.Offset(0, 81).Value
                .Nov6.Value = c.Offset(0, 82).Value
                .Dec6.Value = c.Offset(0, 83).Value
                .Jan7.Value = c.Offset(0, 84).Value
                .Feb7.Value = c.Offset(0, 85).Value
                .Mar7.Value = c.Offset(0, 86).Value
                .Apr7.Value = c.Offset(0, 87).Value
                .May7.Value = c.Offset(0, 88).Value
                .Jun7.Value = c.Offset(0, 89).Value
                .Jul7.Value = c.Offset(0, 90).Value
                .Aug7.Value = c.Offset(0, 91).Value
                .Sep7.Value = c.Offset(0, 92).Value
                .Oct7.Value = c.Offset(0, 93).Value
                .Nov7.Value = c.Offset(0, 94).Value
                .Dec7.Value = c.Offset(0, 95).Value
                .Jan8.Value = c.Offset(0, 96).Value
                .Feb8.Value = c.Offset(0, 97).Value
                .Mar8.Value = c.Offset(0, 98).Value
                .Apr8.Value = c.Offset(0, 99).Value
                .May8.Value = c.Offset(0, 100).Value
                .Jun8.Value = c.Offset(0, 101).Value
                .Jul8.Value = c.Offset(0, 102).Value
                .Aug8.Value = c.Offset(0, 103).Value
                .Sep8.Value = c.Offset(0, 104).Value
                .Oct8.Value = c.Offset(0, 105).Value
                .Nov8.Value = c.Offset(0, 106).Value
                .Dec8.Value = c.Offset(0, 107).Value
                .Jan9.Value = c.Offset(0, 108).Value
                .Feb9.Value = c.Offset(0, 109).Value
                .Mar9.Value = c.Offset(0, 110).Value
                .Apr9.Value = c.Offset(0, 111).Value
                .May9.Value = c.Offset(0, 112).Value
                .Jun9.Value = c.Offset(0, 113).Value
                .Jul9.Value = c.Offset(0, 114).Value
                .Aug9.Value = c.Offset(0, 115).Value
                .Sep9.Value = c.Offset(0, 116).Value
                .Oct9.Value = c.Offset(0, 117).Value
                .Nov9.Value = c.Offset(0, 118).Value
                .Dec9.Value = c.Offset(0, 119).Value
                .Jan10.Value = c.Offset(0, 120).Value
                .Feb10.Value = c.Offset(0, 121).Value
                .Mar10.Value = c.Offset(0, 122).Value
                .Apr10.Value = c.Offset(0, 123).Value
                .May10.Value = c.Offset(0, 124).Value
                .Jun10.Value = c.Offset(0, 125).Value
                .Jul10.Value = c.Offset(0, 126).Value
                .Aug10.Value = c.Offset(0, 127).Value
                .Sep10.Value = c.Offset(0, 128).Value
                .Oct10.Value = c.Offset(0, 129).Value
                .Nov10.Value = c.Offset(0, 130).Value
                .Dec10.Value = c.Offset(0, 131).Value
        
                .Del1.Enabled = True    'allow record deletion
                .Add1.Enabled = False      'don't want to duplicate record
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                MsgBox "There are " & f & " instances of " & strFind
                Me.Height = 318
            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
At the very top, setting
Code: 
Set rSearch = Sheet2.Range("A2", "A65536").End(xlUp)
does nothing more than set rSearch to cell A1.

Maybe, your intent was the untested
Code: 
with sheet2
set rSearch=.Range(.range("A2"),.range("A65536").End(xlUp))
    end with
Also, I don't know what the intent of the code is but there has to be a better way to design a solution other than coding that many individual controls.
 
Upvote 0
Thanks Tushar but when I tried the code it returned a syntax error message.

Roy Cox's sample apparently searches all the data not only A1.

I agree, the codes were too long, I'm in urgent to complete the first model of the userform, would certainly appreciate any suggestion how to shorten the codes, Ive been trying to understand the use of array but to no avail.

Helmy
 
Upvote 0
Helmy

To give any advice in regards shortening the code it would be helpful to know what it actually does.:)

For a start this.
Code: 
For i = 1 To 10
    For j = 1 To 12
    
        strname = Format(DateSerial(2006, j, 1), "mmm") & i
        col = 12 + 12 * (i - 1) + j - 1
        Me.Controls(strname) = c.Offset(0, col).Value
    Next j
Next i
Could replace this.
Code: 
                .Jan1.Value = c.Offset(0, 12).Value
                .Feb1.Value = c.Offset(0, 13).Value
                .Mar1.Value = c.Offset(0, 14).Value
                .Apr1.Value = c.Offset(0, 15).Value
                .May1.Value = c.Offset(0, 16).Value
                .Jun1.Value = c.Offset(0, 17).Value
                .Jul1.Value = c.Offset(0, 18).Value
                .Aug1.Value = c.Offset(0, 19).Value
                .Sep1.Value = c.Offset(0, 20).Value
                .Oct1.Value = c.Offset(0, 21).Value
                .Nov1.Value = c.Offset(0, 22).Value
                .Dec1.Value = c.Offset(0, 23).Value
                .Jan2.Value = c.Offset(0, 24).Value
                .Feb2.Value = c.Offset(0, 25).Value
                .Mar2.Value = c.Offset(0, 26).Value
                .Apr2.Value = c.Offset(0, 27).Value
                .May2.Value = c.Offset(0, 28).Value
                .Jun2.Value = c.Offset(0, 29).Value
                .Jul2.Value = c.Offset(0, 30).Value
                .Aug2.Value = c.Offset(0, 31).Value
                .Sep2.Value = c.Offset(0, 32).Value
                .Oct2.Value = c.Offset(0, 33).Value
                .Nov2.Value = c.Offset(0, 34).Value
                .Dec2.Value = c.Offset(0, 35).Value
                .Jan3.Value = c.Offset(0, 36).Value
                .Feb3.Value = c.Offset(0, 37).Value
                .Mar3.Value = c.Offset(0, 38).Value
                .Apr3.Value = c.Offset(0, 39).Value
                .May3.Value = c.Offset(0, 40).Value
                .Jun3.Value = c.Offset(0, 41).Value
                .Jul3.Value = c.Offset(0, 42).Value
                .Aug3.Value = c.Offset(0, 43).Value
                .Sep3.Value = c.Offset(0, 44).Value
                .Oct3.Value = c.Offset(0, 45).Value
                .Nov3.Value = c.Offset(0, 46).Value
                .Dec3.Value = c.Offset(0, 47).Value
                .Jan4.Value = c.Offset(0, 48).Value
                .Feb4.Value = c.Offset(0, 49).Value
                .Mar4.Value = c.Offset(0, 50).Value
                .Apr4.Value = c.Offset(0, 51).Value
                .May4.Value = c.Offset(0, 52).Value
                .Jun4.Value = c.Offset(0, 53).Value
                .Jul4.Value = c.Offset(0, 54).Value
                .Aug4.Value = c.Offset(0, 55).Value
                .Sep4.Value = c.Offset(0, 56).Value
                .Oct4.Value = c.Offset(0, 57).Value
                .Nov4.Value = c.Offset(0, 58).Value
                .Dec4.Value = c.Offset(0, 59).Value
                .Jan5.Value = c.Offset(0, 60).Value
                .Feb5.Value = c.Offset(0, 61).Value
                .Mar5.Value = c.Offset(0, 62).Value
                .Apr5.Value = c.Offset(0, 63).Value
                .May5.Value = c.Offset(0, 64).Value
                .Jun5.Value = c.Offset(0, 65).Value
                .Jul5.Value = c.Offset(0, 66).Value
                .Aug5.Value = c.Offset(0, 67).Value
                .Sep5.Value = c.Offset(0, 68).Value
                .Oct5.Value = c.Offset(0, 69).Value
                .Nov5.Value = c.Offset(0, 70).Value
                .Dec5.Value = c.Offset(0, 71).Value
                .Jan6.Value = c.Offset(0, 72).Value
                .Feb6.Value = c.Offset(0, 73).Value
                .Mar6.Value = c.Offset(0, 74).Value
                .Apr6.Value = c.Offset(0, 75).Value
                .May6.Value = c.Offset(0, 76).Value
                .Jun6.Value = c.Offset(0, 77).Value
                .Jul6.Value = c.Offset(0, 78).Value
                .Aug6.Value = c.Offset(0, 79).Value
                .Sep6.Value = c.Offset(0, 80).Value
                .Oct6.Value = c.Offset(0, 81).Value
                .Nov6.Value = c.Offset(0, 82).Value
                .Dec6.Value = c.Offset(0, 83).Value
                .Jan7.Value = c.Offset(0, 84).Value
                .Feb7.Value = c.Offset(0, 85).Value
                .Mar7.Value = c.Offset(0, 86).Value
                .Apr7.Value = c.Offset(0, 87).Value
                .May7.Value = c.Offset(0, 88).Value
                .Jun7.Value = c.Offset(0, 89).Value
                .Jul7.Value = c.Offset(0, 90).Value
                .Aug7.Value = c.Offset(0, 91).Value
                .Sep7.Value = c.Offset(0, 92).Value
                .Oct7.Value = c.Offset(0, 93).Value
                .Nov7.Value = c.Offset(0, 94).Value
                .Dec7.Value = c.Offset(0, 95).Value
                .Jan8.Value = c.Offset(0, 96).Value
                .Feb8.Value = c.Offset(0, 97).Value
                .Mar8.Value = c.Offset(0, 98).Value
                .Apr8.Value = c.Offset(0, 99).Value
                .May8.Value = c.Offset(0, 100).Value
                .Jun8.Value = c.Offset(0, 101).Value
                .Jul8.Value = c.Offset(0, 102).Value
                .Aug8.Value = c.Offset(0, 103).Value
                .Sep8.Value = c.Offset(0, 104).Value
                .Oct8.Value = c.Offset(0, 105).Value
                .Nov8.Value = c.Offset(0, 106).Value
                .Dec8.Value = c.Offset(0, 107).Value
                .Jan9.Value = c.Offset(0, 108).Value
                .Feb9.Value = c.Offset(0, 109).Value
                .Mar9.Value = c.Offset(0, 110).Value
                .Apr9.Value = c.Offset(0, 111).Value
                .May9.Value = c.Offset(0, 112).Value
                .Jun9.Value = c.Offset(0, 113).Value
                .Jul9.Value = c.Offset(0, 114).Value
                .Aug9.Value = c.Offset(0, 115).Value
                .Sep9.Value = c.Offset(0, 116).Value
                .Oct9.Value = c.Offset(0, 117).Value
                .Nov9.Value = c.Offset(0, 118).Value
                .Dec9.Value = c.Offset(0, 119).Value
                .Jan10.Value = c.Offset(0, 120).Value
                .Feb10.Value = c.Offset(0, 121).Value
                .Mar10.Value = c.Offset(0, 122).Value
                .Apr10.Value = c.Offset(0, 123).Value
                .May10.Value = c.Offset(0, 124).Value
                .Jun10.Value = c.Offset(0, 125).Value
                .Jul10.Value = c.Offset(0, 126).Value
                .Aug10.Value = c.Offset(0, 127).Value
                .Sep10.Value = c.Offset(0, 128).Value
                .Oct10.Value = c.Offset(0, 129).Value
                .Nov10.Value = c.Offset(0, 130).Value
                .Dec10.Value = c.Offset(0, 131).Value
 
Upvote 0
Thanks Norie for the tips, it certainly make sense to understand what the code actually does.

I tried this on the project to replace the long winded code but mmm ...it was returned with Variable Not Defined prompt on strname....

Cheers

Learning .....Slowly
Helmy
 
Upvote 0
Helmy

Just add this.
Code: 
Dim strName As String
Dim I As Long
You must have Option Explicit set up which is a good thing.:)
 
Upvote 0
Thanks Norie. It works well with my userform. Guess I'll just have to get myself familiarised with the codes and terms before plunging into complicated programming and of course learn from pros like you.

Appreciate your help very much :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Do Loop not returning first two rows of data in Range
Replies
4
Views
317
programsam
P
N
  • Solved
Copy headers across sheets no longer works.
Replies
7
Views
413
NoSparks
N
M
  • Question
Searching From a Userform and showing results in a ListBox
Replies
2
Views
2K
DanteAmor
DanteAmor
M
  • Question
.SelStart/.SelLength Not Working on Multi-Step UserForm
Replies
2
Views
588
sijpie
sijpie
D
  • Question
Inventory workbook issue Search stopped working
2
Replies
15
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,214,944
Messages
6,122,387
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top