+ Reply to Thread
Results 1 to 8 of 8

Substitute code partly working

  1. 01-19-2010, 03:08 PM #1
    drewship is offline
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Substitute code partly working

    Hello all. I have an interesting problem. I have the following code block in my spreadsheet. The first 'With' section works every time. The second 'With' section only works if I step through the code manually using F8. If I run the the entire macro using the Process button, it seems like the second 'With' block is just skipped alltogether. I tried commenting out the first 'With' block and when the Process button is pressed, the second 'With' section still seems to be skipped. I have attached a copy of the workbook. Can someone help fix this?

    Please Login or Register  to view this content.
    Thanks,
    Andrew
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-19-2010, 03:20 PM #2
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Substitute code partly working

    So small, but so important - you're missing two dots in this line:
    Please Login or Register  to view this content.
    If you follow Domski's suggestion too, you can dispense with the two loops.
    Last edited by StephenR; 01-19-2010 at 03:23 PM.
    Reply With QuoteRegister To Reply
  3. 01-19-2010, 03:20 PM #3
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Substitute code partly working

    I think you missed some .'s:

    Please Login or Register  to view this content.
    You could also use Find/Replace instead of the Substitute function.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
    Reply With QuoteRegister To Reply
  4. 01-19-2010, 03:26 PM #4
    drewship is offline
    Forum Contributor
    Join Date
    04-30-2009
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    496

    Re: Substitute code partly working

    Great!!! Thanks to both of you!!!
    Reply With QuoteRegister To Reply
  5. 01-19-2010, 03:53 PM #5
    rwgrietveld is offline
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Substitute code partly working

    The dots in the suggested code(s) works.

    Nevertheless I feel I must comment
    Please Login or Register  to view this content.
    will return a range from A to B

    As this range is on the same sheet use
    Please Login or Register  to view this content.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com     (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com     (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo
    Reply With QuoteRegister To Reply
  6. 01-19-2010, 03:58 PM #6
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Substitute code partly working

    rwgrietveld: is that really necessary?
    Reply With QuoteRegister To Reply
  7. 01-19-2010, 04:12 PM #7
    rwgrietveld is offline
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Substitute code partly working

    Stephen,

    No it isn't, IMO best is to use .Range(.Range, .Range) as range is a worksheets property itself
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    I see many constructions and it seems people 'fiddle' till it works.

    Maybe nice to start a topic on this.
    Last edited by rwgrietveld; 01-19-2010 at 04:14 PM.
    Reply With QuoteRegister To Reply
  8. 01-19-2010, 04:54 PM #8
    StephenR is offline
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Substitute code partly working

    There are indeed many ways to skin a cat. I wondered if any particular one was more efficient or rigorous than the others.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1