Posts by larbec

    Re: Help writing a Macro to clear out all cells


    If you need an external book too I can provide one but I would think as long as you had the input name or just leave that part blank for me to fill in is fine. One book is called (SSD (Z:) EXCEL SHEETS........... They are all linked to a book in this drive

    I would like a Macro to clear out all the data on my sheet and replace it with new data from the next row(s)up. My sheet is linked to 4 external books and will be getting data from 18 sheets per book. How can this be done? This data I need to link from external books are cells CDEF NOPQ XYZ AA 3:35. I do not have any issues linking or anything else only with clearing out the data and placing the new data 1 row up from the external sheets and the other data comes from the "Drawn Numbers" tab within this same book. I know I can do it by copy and paste and also write a hard formula but would like a Macro (I am not familiar enough to do myself) so I can just copy and paste into my other books.


    Thanks Gurus!!!


    http://forum.chandoo.org/threa…ste-new-row-s-data.17612/

    Files

    • ND Sheet.xlsx

      (55.22 kB, downloaded 85 times, last: )

    Re: Automate copying rows based on counter number increment


    I am trying to duplicate what is happening in columns HIJK 30-44 in columns OPQR and VWXY. E and U are in columns HI OP and O and D are in columns JK QR. I will have 2 rows of numbers as in rows 43 and 44 OPQR and VWXY.


    If 1st movement is a U or E column(s) H or I OR O or P will advance UP 1 digit from 1111 to 2111 or 1211 in OPQR or 3222 or 2322 in VWXY.


    If a 1st movement is a O or a D column(s) Q or R OR X or Y will advance up 1 digit from 1111 to 1121 or 1112 in OPQR or 2222 to 2232 or 2223 in VWXY.


    2nd movement UP row 41 will have to be in column(s)P if U in OPQR or V in VWXY. You can not advance up a row if you have a single cell like the 3 in O42 or the 3 in W42


    3rd movement Up is an O or D so either Q or R in OPQR will go up or X or Y in VWXY. Because there is an even number of cells like 4 1's vertically in columns Q and R rows 41-44 and columns X and Y in rows 41-44 either column can advance Up. I will pick Q and Y to go Up


    4th movement Up is another O or D so again it will move in Q or R OR X or Y. Because there is a single cell in Q40 and Y40 columns R and X have to go up even though there is an Odd amount of cells vertically 40-44 (OPQR VWXY) The only place it can advance Up with an od number of cells is from the starting point on the bottom rows. After it gets a foundation of even number of cells vertically it will stay even.


    The next movement Up is an E or U so because there is a even number of cells vertically in columns O 39-42 and W 39-42 they are the ones that advance Up


    The next one to advance is another E or U so the P or V will go Up


    This is how the counters move up. The conditional formatting is very important so I need to have the green font with gray shade and red font with yellow shade


    I hope this makes better sense and again. THANK YOU for your assistance

    Re: Automate copying rows based on counter number increment


    I just realized my spread sheet was inaccurate as far as the conditional formatting so my apologies. This one is correct. This video has sound and hopefully someone will be able to help. I REALLY appreciate all the assistance thus far. I just realized my spread sheet was inaccurate as far as the conditional formatting so my apologies. This one is correct. This video has sound and hopefully someone will be able to help. I REALLY appreciate all the assistance thus far. I can have 1 button per row or 1 button to do them advance all at once


    Video is on its way. Here is a corrected file with proper CF

    Re: Automate copying rows based on counter number increment


    No flame here Roy


    I deleted them due to the sensitivity Some of my books are 2 Gigs each and hard to make examples so my apologies if it has caused any issues. That's definitely not my intentions. Hopefully what I posted will be enough information to give me my end results. I am doing it one cell or row at a time and have to to 90 of these (each 4 columns) per book. Each book has 60 sheets x 4 to 5 books which is extremely time consuming. So, if this can even be done it will surly speed up my time but more importantly no mistakes. Seems being human I make mistakes lol.


    I know someone can figure this out. Honestly, this board has some of the BEST programmers I've ever run across. Kjbox is by far the bomb that guy is AMAZING and I REALLY appreciate all his hard work, effort and knowledge. I'm learning VBA but have a long way to go so I also appreciate everyones patience and THANK YOU Oz for a FANTASTIC site and tutorial


    Blessing!

    I will try my best to explain this. I have 4 digit counters that always count up 1-2-3-4-5-6-7-8-9. When they reach 9 they start over again with 1 so 1-2-3-4-5-6-7-8-9-1


    The way they work is the 1st and 3rd digit is either Even or UP, the 2nd and 4th digit is either Odd or down. (even though they always go up LOL) Even and UP is Green in color and Odd or Down is red. Right now if I want to copy what the counter does in say columns HILK OPQR VWXY I either have to copy and paste each row or click on cells 1H 1I 1J 1 K etc.... I was wondering..... if I place a new counter number in two rows .... can it do it automatically run without me having to do one row at a time. I would like to place the 2 counter numbers in AC AD AE AF 3010 and 3011, AJ AK AL AM , AQ AR AS AT. Place the U/D or E/O vertically in column(s) AG AN AU and it will do the counters automatically for me by clicking on a button that says "go". Does this make sense?


    Is this possible?
    I want it to go from rows 3010 to 3060


    Is this possible?


    Thanks for looking this


    I am trying to shorten worksheet so I can upload it

    Re: Other sheets will not work in book


    I understand the red is as red as I will get lol, the O and D need to be "red" right now they are green. The E's and Us need to be "green".


    Also, how do I change the size of the font and style. Presently its larger than the one I have and I have to reformat every time I click on a cell. I am using 11 Calibri for my numbers (hate to be a PITA) and I do not mind changing it


    Thanks Kj

    Re: Other sheets will not work in book


    Everything is working perfect except the sheet is giving me the date after the date beginning with column (AB AI AP AW BD BK BR BY CF ....MN) these columns should be empty




    [ATTACH=CONFIG]60778[/ATTACH]


    This is very minor and I have been messing with the Vba code but cannot seem to find the right color. The E's and U's should be bold dark green, the O's and D's should be dark bold red