Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error In Code (1997)

    I am having trouble with the following lines of code:

    Dim strBefore As String, strCurrent As String, strNext As String
    strBefore = "<" & CStr(Date) & " 18:00"
    strCurrent = ">=" & CStr(Date) & " 18:00"
    strNext = CStr(Date + 1) & " 18:00"
    ' build worksheets
    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = Replace(CStr(Date), "/", "-")

    The problem it seems I am having is the the "replace" command in the bottom line.

    It seems to work fine in XP, but when exporting to 2000, I receive an error (Sub or Function not defined).

    Is this command not recognized in 2000?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error In Code (1997)

    Strange indeed. The Replace function was added in XL2000, so should work there. Sure it isn't XL97?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error In Code (1997)

    My bad.

    It is Excel 97 that's giving me fits.

    Company "standards" aren't quite so standard.

    Besides upgrading to 2000 or XP any suggestions?

    If you can edit the title to correct my error, that would be OK with me.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Error In Code (1997)

    Excel 97 does not have a Replace function built in.

    If you include the following UDF in your module it should work.

    Function Replace(strOrigin As String, strFind As String, strReplace As String)
    Dim lPos As Long, lPrevPos As Long, iFindLen As Integer
    lPos = InStr(1, strOrigin, strFind)
    iFindLen = Len(strFind)
    lPrevPos = lPos
    Do Until lPos = 0
    Replace = Left(strOrigin, lPos - 1) + strReplace + Right$ _
    (strOrigin, Len(strOrigin) - (lPos - 1 + iFindLen))
    lPos = InStr(lPrevPos + Len(strReplace), strOrigin, strFind)
    lPrevPos = lPos
    Loop
    End Function

    Andrew C

    <small>P.S. I altered your post title.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Error In Code (1997)

    Since you are using today's date, I think this will work in 97, as the format is implicity converted to String. Edit the format to requirements, and don't run it at midnight <img src=/S/grin.gif border=0 alt=grin width=15 height=15>:

    Worksheets(Worksheets.Count).Name = Format(Date, "yyyy/mm/dd")
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error In Code (1997)

    I would do it this way:

    <pre>Dim strBefore As String, strCurrent As String, strNext As String
    Dim oNewSheet As Worksheet
    strBefore = "<" & Format(Date, "mm/dd/yyyy") & " 18:00"
    strCurrent = ">=" & Format(Date, "mm/dd/yyyy") & " 18:00"
    strNext = Format(Date + 1, "mm/dd/yyyy") & " 18:00"
    Set oNewSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oNewSheet.Name = Format(Date, "mm-dd-yyyy")
    </pre>

    Legare Coleman

Posting Permissions

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