Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find/Replace errors (xl2000/xl2002)

    I wrote code that replaces part of a formula on a worksheet table. It works fine in Excel 2002 but fails in Excel 2000. <img src=/S/barf.gif border=0 alt=barf width=64 height=23> I've attached a sample (with fake data).

    The problem is that I have two price sheets and one look up table. I need to dynamically swap the formulas to look at price-sheet-1 or price-sheet-2 (both of which are laid out identically). The user selects a radio button (check box in the attached sample) to choose between two options. The code then replaces the sheet name in this look up table.

    I know I could do this with INDIRECT or VLOOKUP but I failed miserably trying to get those to work and decided to do it in code instead. (These formulas are really nested and too complex for my brain to figure out.)

    Can someone look at this file and tell me how to fix it such that it works in both versions of Excel? The attached file has links to the 'real' workbook so that part will fail for you but you can ignore that for the sake of this exercise. Any formulas will work as a test, I just need to get the find/replace to work. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Thnx, Deb

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Find/Replace errors (xl2000/xl2002)

    Hi Deb,

    This simplified version of your macro seems to work OK in Excel 2000:

    Public Sub UpdatePrice(isDSL As Boolean)
    Dim oldSheet As String, newSheet As String
    If isDSL Then
    oldSheet = "-T1"
    newSheet = "-DSL"
    Else
    oldSheet = "-DSL"
    newSheet = "-T1"
    End If
    ThisWorkbook.Worksheets("PriceData").Range("_tblPr ice").Replace What:=oldSheet, Replacement:=newSheet, LookAt:=xlPart
    Range("F12").Select
    End Sub

    Cheers
    PS: Excel 2000 doesn't support LookIn:=xlFormulas in Search/Replace
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find/Replace errors (xl2000/xl2002)

    Great!! Thank you very much. I've never used the simpler .Replace for a Range. I had always used .Find and then do whatever I needed on the resultant range. Your solution is much better for this problem. I know that .Find works ok with LookIn:=xlFormulas in Excel2000 (it's even mentioned in the help) but it was the replace part that puked.

    Now I can send off this workbook to my co-worker only four hours later than I said I'd have it done <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    You saved my tush on this one! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Thnx, Deb

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find/Replace errors (xl2000/xl2002)

    Just a follow up... I had a chance to test this same code in Excel97 and it fails!

    I could only get it to work if the sheet with this lookup table is the active sheet. Excel 2000/2002 doesn't care. I've run into this problem with other things for Excel97. I've run across several things that don't work unless the sheet in question is active. It drives me nuts. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    This pricing sheet is set to veryhidden so I have to unhide it, activate it, do the replace, then re-hide (set to veryhidden). I think I'll only do this if the user is running Excel97 and not make the other versions take the hit. Good thing I have all three versions to test but it greatly increase the time it takes to develop all these tools when I have to test in all versions.

    So if anyone tells you VBA (even simple, trivial stuff) is compatible across versions, they're not talking from experience. <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

    Thnx, Deb

Posting Permissions

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