Posts by EXCELn00b18

    Basically I have 8 route and 8 drivers. Every week the route a driver has changes on a rotating schedule. The table below is an example of how it works. I creating a 1 page print out as an overview for the week to give to our dispatch and on this sheet i need to have who is on what route. I am trying to find a way to only display 1 week at a time.


    My idea was to use WEEKNUM() to find the week number and then use some formula to convert that to only 1-8. For example weeks week 9 would instead be week 1 and so on. There is 2 issues with that, 1) I cant figure out the math to get that working and 2) when the year starts over you would go from 4 (week 52) back to 1 which messes up the rotation.


    I am not opposed to using VBA, I just started out with formulas because i understand them a little bit better and was hoping to figure this out on my own.


    Any help is much appreciated.


    Week of7/5/20217/12/20217/19/20217/26/20218/2/20218/9/20218/16/2021
    route 1driver 1driver 2driver 3driver 4driver 5driver 6driver 7
    route 2driver 2driver 3driver 4driver 5driver 6driver 7driver 8
    route 3driver 3
    driver 4driver 5driver 6driver 7driver 8driver 1
    route 4driver 4driver 5driver 6driver 7driver 8driver 1driver 2
    route 5driver 5driver 6driver 7driver 8driver 1driver 2driver 3
    route 6driver 6driver 7driver 8driver 1driver 2driver 3driver 4
    route 7driver 7driver 8driver 1driver 2driver 3driver 4driver 5
    route 8driver 8driver 1driver 2driver 3driver 4driver 5driver 6

    I am trying to make a formula that will see if a column contains either "R", "B", or "D". If it does contain on of those then return a "Y" otherwise return a "N".


    =IF((ISNUMBER(FIND({"R","B","D"},[A.B.C.D.]))),"Y","N")


    This formula almost works but will only return a "Y" if the cell contains an "R" or whichever one I first.


    EX.


    =IF((ISNUMBER(FIND({"R","B","D"},[A.B.C.D.]))),"Y","N") ONLY RETURNS IF IT CONTAINS "R"


    =IF((ISNUMBER(FIND({"D","R","B"},[A.B.C.D.]))),"Y","N") ONLY RETURNS IF IT CONTAINS "D"


    =IF((ISNUMBER(FIND({"B","D","R"},[A.B.C.D.]))),"Y","N") ONLY RETURNS IF IT CONTAINS "B"


    [A.B.C.D.] is the column that we are looking at and it has values that look like the following:


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 72"]

    [tr]


    [td]


    99992BY

    [/td]


    [/tr]


    [tr]


    [td]

    9309 RY

    [/td]


    [/tr]


    [tr]


    [td]

    0513 BYL

    [/td]


    [/tr]


    [tr]


    [td]

    8018 Y

    [/td]


    [/tr]


    [tr]


    [td]

    W0319 RN

    [/td]


    [/tr]


    [tr]


    [td]

    0312 BN

    [/td]


    [/tr]


    [tr]


    [td]

    9708 BY

    [/td]


    [/tr]


    [tr]


    [td]

    0412 BY

    [/td]


    [/tr]


    [tr]


    [td]

    0412 BY

    [/td]


    [/tr]


    [/TABLE]
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 48"]

    [tr]


    [TD="width: 64"] [/TD]

    [/tr]


    [/TABLE]

    How to insert row including formula to the end of a table using VBA? I have a formula to generate the next number in my table, but as of right now the only way i can find to add another row is to tab all the way to the end of the table. Is there a way to automatically add a row and insert that formula to the end of the table?

    I can't seem to get any of the usual functions I use (Vlookup, Match, Error) to work for what I need, I am wanting to See if The value in column A in the table is included in Column L. The Reason I need this is because I only need to show the Row if Cell A of the row is in column L.


    Column K is the column in which i was using to notate weather the value was in column L

    Files

    • Sean Tmp.xls

      (1.27 MB, downloaded 88 times, last: )

    I have a Column of cells that I get from another program. In the column some cells are blank, some cells have 1 number, and then some cells have several numbers separated by 3 spaces EX.(1 1 5 4). Is there a way that I can add these values together in one cell so in my example i would add them in to one cell valuing 11?

    I have read that with VBA you can manipulate websites in IExplorer. I currently use a web site that I have to regularly check and if there is a link i have to click on it. It is pretty much a race between all of the other locations in my company to confirm it first. Is there a way to automate this process with VBA or do i need to look into something else?

    I have gotten my code working almost perfectly thanks to help from dangelor. The only issue i have now is when it prints the file, it prints one page with just the top row because that is where the first page break is. I have attached my new code if anyone could please help me.


    I have a report that I run several times a day and I need to format it in excel. I have got my VBA macro working almost right but I have a few things I cant figure out


    I have pasted my macro with notes in the places I need help. I need to copy row one and paste it after every page break so that it ends up at the top of each page. I also need to fill K2 and L2 down to the last row after inserting the formula in K2



    Thank you for any and all help