Posts by Justin Doward

    The with just allows a bit more flexibility and can save time if you add to the code later, the with can incorporate a number manipulations to the chosen object, range etc... without having to type that text each time similar to the use of declaring ws as a worksheet then setting it to a specific worksheet can allow you to modify a single line of code but adapts the requirement throughout the sub... it also allows you to move the code easily between forms and sheets often by changing only a couple of pieces of the code rather than looking through and finding every reference to the objects and ranges you need to change. As you note though, if you are only ever going to perform a single manipulation it probably simplifies the code to reference the object directly, if you intend to get more involved in VBA programming then making your code as adaptable as possible from the start is a good idea and you learn a great deal as you crash into the difficulties that arise in declaring and setting objects using variables on sheets, forms or within the code.


    That is my understanding, there may be more to it if one of the programmers wants to jump in, glad my earlier response helped to fix your current issue.

    Hi Jimmy,


    I think it is due to the activesheet, you either need to activate each sheet prior to starting the instruction, or preferably change the code to avoid the activesheet requirement.


    eg:


    Code
    1. for each sh in worksheets
    2. for each shp in sh.shapes
    3. your code
    4. next shp
    5. next sh

    HTH

    HI Stan,


    the syntax for range using .cells is .range(.cells(row, column),.cells(row,column)) so to get D24:D41 using this syntax you need somthing like:


    Don't forget to wrap the code in your post, just highlight and click the code </> icon.


    Regards

    Justin

    I think this is what you want to do, the code could be more efficient if bypassing the clipboard copyandpaste, but I am not sure how to do the transpose in that case.





    Regards

    Justin

    This formula achieves the same result in B5 and dragged across the range:


    =TEXT($U$2 + 7 * (COLUMN()-2),"mmm

    dd

    yy")


    It is often better to post the scenario you are trying to achieve rather than assuming the solution, there are probably better formulas than this one, but it does the job.

    You have spaces in your formula, I did not realise - modify to:

    =DATE(YEAR(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2)),MONTH(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2)),DAY(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2))+7)


    and then to:

    =TEXT(DATE(YEAR(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2)),MONTH(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2)),DAY(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2))+7),"mmm dd yy")

    Hi Billy,


    Not sure why you do not just reference U2 again rather than referencing the text, but you can try this formula:

    =DATE(YEAR(MID(A1,4,2)&LEFT(A1,3)&RIGHT(A1,2)),MONTH(MID(A1,4,2)&LEFT(A1,3)&RIGHT(A1,2)),DAY(MID(A1,4,2)&LEFT(A1,3)&RIGHT(A1,2))+7)


    Justin

    Change your syntax to the following:

    Hi Musicboi,


    I have rewritten a couple of lines so they work, I will be able to check back later but that is it for now.