Consulting

Results 1 to 3 of 3

Thread: Range.Find Issues

  1. #1
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location

    Range.Find Issues

    Hi Folks

    I'm trying to use the Range.Find method to get a column number, which seems pretty straight forward. However, the results seem quite random at the moment...that is one time it works and another time I get an error 91. Very odd.

    In detail, I've got a sub that's supposed to get said column number and nothing more, like so:

    numSpaltePlanHeute = rngPlanDatenSpalten.Rows(numZeilePlanRef).Find(What:=DateValue(Date), _
                    LookIn:=xlFormulas).Column - numSpaltePlanRef
    When calling this procedure on it's own it's working. When calling it from a Worksheet_Activate event it works as well. But when calling it from the Worksheet_BeforeSave sub it may work or throw the error 91. I can't find the logic/pattern behind this behaviour.

    Worth mentioning is that the find range contains dates...I've read that the find method can get weird with dates.

    Any ideas what I'm missing?

    Cheers

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Maybe some "dates" are actually Text Strings and won't show if LookIn:=formulas.

    It might help if you
    Dim Dte As String
    Dte = Format(Date, "mm-dd-yy") 'or as normally formatted on the sheet
    Then Find Dte and Lookin xlValues, while looking at xlPart
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Application.Match(CLng(Date), rngPlanDatenSpalten.Rows(numZeilePlanRef), 0)

Posting Permissions

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