1

thank you very much to those who answered my original question. I'd like to go into a little more detail about the problem I am having. I am working on a macros that provides a visual representation of the 4 different production phases of a product we produce. The spreadsheet is set up to appear as a linear calendar, showing all 365 days of the year and grouping them into calendar weeks. Each production phases has a different color associated with it. The four buildphases are: 1) Assembly: 1 day (yellow) 2) Initial analysis: 1 day (purple) 3) In-depth analysis: 7 days (green) 4) Shipping: 1 day (red)

No work is performed in the facility on weekends or holidays. Saturdays and sundays are represented by a cell colored black and holidays are represented by cells colored orange and having a criss cross pattern. The macro is designed to skip these holidays using the following if then statement:

Dim i As Integer

i = Analysis days

for i = 1 to 7

ActiveCell.Select

If Selection.Interior.Pattern = x1CrissCross And Selection.Interior.Color = orange Then

ActiveCell.Offset(0, 1).Select

Else: ActiveCell.Select

End If

If Selection.Interior.Color = black Then

ActiveCell.Offset(0, 2).Select

Else: ActiveCell.Select

End If.

The If Then statement that tells the macro to skip Saturday and Sunday works every time. But the statement that tells the macro to skip holidays only works part of the time, and if the holidays last longer than a few days (Christmas vacation lasts nine days) the macro inserts production work days interspersed throughout the holiday. I've found that by copying and pasting the above statements directly beneath one another several times seems to fix the issue. But I am sure there has to be a more efficient way to do this. Does anyone know of a way I can fix the issue without having to copy and paste the same lines of code mulitple times?

Thanks in advance!

Alec Terry
  • 37
  • 1
  • 6
  • Could you make your if/Then into a Private Sub, and just call that with one line in each sub? Do you know what I mean? – BruceWayne Apr 15 '16 at 19:59
  • 2
    If you have code that works, but is just ugly or inefficient or otherwise in need of "another pair of eyes", you'll be happy to learn that there's [codereview.se] waiting to lay eyes on your code and provide insightful feedback on any & all aspects of it. – Mathieu Guindon Apr 15 '16 at 20:09
  • Also you can copy/paste code, instead re-typing the same code. – Davesexcel Apr 15 '16 at 20:54
  • i'd use a function instead of a sub – Patrick Lepelletier Apr 15 '16 at 23:17
  • Hi everyone thanks so much for your fast answers. I apologize for the delayed response. I am working on the post to add new information and a quick fix I have discovered. – Alec Terry Apr 20 '16 at 12:08

1 Answers1

0

If you are looking for a way for make a test and store the result globally in your code, so also in the other functions/subs you can access the data prevoiusly tested take a look here.

If you are lookin to a way to compact your code and write something like 

b = check(a)

instead of

if a="xx" then
   b = "something"
else
   b = "something else"
end if

take a look to this link for functions/subs explanations

Community
  • 1
  • 1
EttoreP
  • 404
  • 6
  • 16