Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA find code nt working 100% (2000)

    I'm using the code below in a worksheet, but for some strange reason, it works for every value but 'Line 1' and i can't see why !

    A button on the sheet has this code attached to it.... (The only interesting bit is in purple) Essentially the button is changing a number, which can be 1,2,3,4,5,6,7,10 . ----- A cell is linked A10, on Weekly Reports tab, = "Line " + this number.
    Then the UpdateNames function is using this text to search for a location between A2:H2 on sheet 'TL'
    It appears to be finding this and working fine, for all values but the first one, 'Line 1' which should match to cell A2.. (but doesn't)
    instead it gives the information under cell H2, which is text 'Line 10'



    '----------------------------------------------------------------------------------------------------------------------------
    <font color=red>Sub LineDown()
    Dim MyDate As Integer
    Sheets("Weekly Reports").Range("F11:F17").ClearContents
    MyDate = ThisWorkbook.Worksheets("Weekly Reports").Range("H6").Value
    MyDate = MyDate - 1
    If MyDate = 0 Then MyDate = 10
    If MyDate = 9 Then MyDate = 7
    Range("H6").Select
    ActiveCell.FormulaR1C1 = MyDate
    <font color=magenta>Call UpdateNames</font color=magenta>
    End Sub</font color=red>
    '----------------------------------------------------------------------------------------------------------------------------

    so its calls this:-

    <font color=red>Sub UpdateNames()

    Application.ScreenUpdating = True

    Dim DataSheet As Worksheet
    Dim FoundLine As Range, SelectedDate As Range
    Dim SearchFor As String

    SearchFor = Workbooks("Weekly_Report.xls").Worksheets("Weekly Reports").Range("A10")

    Set DataSheet = Workbooks("Weekly_Report.xls").Worksheets("TL")
    Set FoundLine = DataSheet.Range("a2:h2").Find(what:=SearchFor)
    Set SelectedDate = Workbooks("Weekly_Report.xls").Worksheets("Weekly Reports").Range("J19")

    If Not FoundLine Is Nothing Then


    SelectedDate.Offset(0, 0) = FoundLine.Offset(1, 0)
    SelectedDate.Offset(1, 0) = FoundLine.Offset(2, 0)
    SelectedDate.Offset(2, 0) = FoundLine.Offset(3, 0)
    SelectedDate.Offset(3, 0) = FoundLine.Offset(4, 0)
    SelectedDate.Offset(4, 0) = FoundLine.Offset(5, 0)
    SelectedDate.Offset(5, 0) = FoundLine.Offset(6, 0)
    SelectedDate.Offset(6, 0) = FoundLine.Offset(8, 0)


    Else
    MsgBox "Error - Cannot find the selected Line."
    End If

    End Sub</font color=red>

    So what its doing is finding some text in the sheet 'TL' from a comparison with cell A10 on the sheet 'Weekly Reports', then copying the information below it from sheet TL, to sheet 'Weekly Reports'
    It works fine for all value's but when its comparing cell A2 instead of displaying the information under Line 1, its displaying the information under L10.

    Further testing shows that its matching to 'Line 10' Before its matching to 'Line 1' so i suspect that the find function needs some more parameters to match exactly. Set FoundLine = DataSheet.Range("a2:h2"). <font color=magenta>Find(what:=SearchFor)</font color=magenta>

    I can't find anying in help, and i don't own an Excel VBA book. so if anyone knows the rest of the parameters for this function i'd be gratefull.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA find code nt working 100% (2000)

    Line1 = Line10 if you are doing a partial match

    Try adding the
    Lookat:=xlWhole
    parameter to the FIND

    Steve
    PS look under FIND in VB help or just enter find in the immediate window and press <F1> for other parameters

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA find code nt working 100% (2000)

    Steve, thanks for the info. thats just what i was looking for. i had been tipped of in a previous post that this function had more parameters, but they didn't go into details.


    bigger thanks for the immediate window tip.. I didn't know you could do that, very very handy for me...


    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA find code nt working 100% (2000)

    Even in your macro code, if you highlight a property or method and hit <F1> you should get help on it.

    Steve

Posting Permissions

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