How to avoid many If Ellse if in my code

  • Hello everyone,

    I worte ths code tonight, because I need a graphic report of how many days the machines are busy, this is my code

    Now it works perfectly, but there are too many Else ifs, and I need to add some more, because I need to arrive at 30 (days) to be represented by colored cells.

    Code
    1. If c.value = J then c.Offset (,1).Resize(1, J)

    I simplified the condition, but what I mean is how do I define a J or an X and tell the code that if cell value is X than he has to color the X number of cells adiacent to that cell? Without having to specify so many conditions?

    Thank you in advance for any input and suggestions!

  • Maybe you don't need any If statements at all, try

    Code
    1. For Each c In ws.Range("C3", ws.Range("C" & ws.Rows.Count).End(xlUp))
    2. c.Offset(, 1).Resize(1, Int(c) + 1).Interior.ColorIndex = 33
    3. Next c

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I am to ask another question/ask for yuor help on this code again.


    I have been trying to find the line of code myself without success.

    I want to add a ondition to the above code, as follows, for each c in range, loop and color each adiacent cell with the value of the cell in range B, BUT only if cell does not contain text. If ANY of the adiacent cells contains text, then skip those cells. Of ocurse the number of cell the code colors must be equal to the value of the cell in column B..

    I want to add this condition because the machine graphic as it is with thIs code is not realistic: it has to skip saturdays and sundays. So in the cells that fall in weekends I will put an X , so the code should skip those and color only the empty cells. How do I indicate this condition in my code?

    I attach a sample of my file, thank you in advance for any suggestion.

  • I see the difficulty you are facing, I will think of a solution. Maybe tomorrow as it is nearly 2AM here!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Try this

    That works directly off the date in Row 5 (checks if it is a Saturday or Sunday) rather than by text in the cells

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • KjBox, it is just perfect. I now have to study the conditions you gave, I had no Idea that vba could "understand" what weekday is by just reading the dates...incredible.

    I really can't express enough apreciation for all your help. Thank you from the heart.

  • You're welcome.


    Basically the main bit of the code works in 3 loops. For each data row in column B it first loops through the dates in row 5 whenever a Saturday is encountered it adds 2 to the integer of the value of the number in the cell in column B plus 1. The code then


    1. For each cell in column B that contains data the code gets the integer of that value and adds 1. that value is stored in the variable lOff


    2. Then it loops through the dates in Row 5 (the value from step 1 sets the number of iterations of the loop), each time a Saturday is encountered it adds 2 to the variable lOff.


    3. The code then loops through the dates again, the number of iterations of the loop is the value of lOff. If the date is not a Saturday or Sunday then the cell for that column in the row of the main loop gets coloured.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thank you Kjbox for the detailed explanation it helps me very much to understand to reasoning of VBA in this specific scenario and thus learn some more.

    I have been googling a lot since yesterday about variables, multiple loops and about lOff. The latter, as I understood your code is the one variable that tells the code what cells to kip....I got this correctly?

    Anyhow, everytime I feel proud of myself and satisfied that I learned something new in vba and I built a code or a piece of code entirely by myself, I get here, I see your codes (of all you experts), and how you reduce a code to few clear concise lines, how you use the variables and the arrays, and how many ways there are to do that...and I feel less proud :)); as I understand I still have to learn so much;

    You are a great source of learning, all this forum!

  • The lOff variable goes through a number of stages


    1. it gets the integer value of the cell in Column B (main loop)


    2. 2 gets added to lOff for each Saturday within the dates set by Int(c) (first inner loop)


    3. finally lOff is used to set the number of dates that the code needs to loop through in order to colour the cells. If a date is a Saturday or Sunday then the code does not actually skip them, it removes any cell background colour that may already be there. (second inner loop)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.