Program (VBA) to test 50x60 table

Hello,

I am working on a file and need some help
In the 1st tab ("Yealry to Daily") , I can define up to 50 tools (from B5 to B54), and allocate those tools on machines per month (from AB5 to CH54)
So it can be on 1 unique machine for the 5 years, or 1 machine for 6 months, then another machine for 1 year, then another one or the previous one, etc....(but usually not more than 3 machines per tool)
Anyway, the attached file is for example, but the final one wil have 13 machines. So with 50 tools and 13 machines, I do not expect to have 1 machine with more than 10 tools. This is why the sheet "Machine #1" has only 10 possible tools

What I would like to do in "Machine #1" is to get in the pink cells all the tool(s) that are allocated on Machine #1, meaning "looking" in the table in "YEarly to Daily" sheet if there are cells with the name "Mach #1"
If there are, the names of those tools should be copied in the pink cells. In my example, I should see Tool1 and Tool2 (in cells B6 and B10) - but not Tool3 since it is not allocated on Machine #1 anytime across the 5 year period (Tool 3 is on Machine #4)

I think I need to do some sort of Loops, but I dont know how to do it....!!!

Thanks in advance

Hi

Try this worksheet activation macro. Right click on the Machine #1 tab, select view code, and paste in the code below. Now when you activate that tab, it will clear out any existing data, and bring in the relevant tool.

Private Sub Worksheet_Activate()
  Dim DataSH As Worksheet
  Set DataSH = Sheets("Yearly to Daily")
  lastrow = 4 + WorksheetFunction.CountA(DataSH.Range("C5:C54"))
  lastcol = DataSH.Cells(4, Columns.Count).End(xlToLeft).Column
  outrow = 2
  'clear out any existing entries
  For i = 6 To 42 Step 4
    Cells(i, 2).Resize(4, 1).ClearContents
  Next i
  For i = 5 To lastrow
    For j = 27 To lastcol
      If DataSH.Cells(i, j) = Range("B1") Then
        outrow = outrow + 4
        Cells(outrow, 2).Value = DataSH.Cells(i, 3).Value
        Exit For
      End If
    Next j
  Next i
  
  
End Sub

rylo

Thanks.....
What do you mean by "When you activate that tab"?

Hi

Select any tab but the machine #1, then select the machine #1 tab and it will fire off the macro.

rylo

Unless I'm doing something wrong, I don't see any change in the sheet "Machine #1".....
And since I am not VBA expert, it's hard for me to decode what it is supposed to do

But in the example, I should get in the sheet "Machine #1" Tool 1 in the cell B6 and Tool 2 in the cell B10 because Tool 1 is loaded on Machine #1 (Mach #1) in the whole row AA5 to CA5, and Tool 2 is also loaded on Machine #1 (Mach #1) in Row 6 from Jan 2013 t Jun 2013, then from Jan 2014 to Jun 2016 (in the sheet Yearly to Daily)
And Tool 3 should not appear in the sheet "Machine #1" because this tool is loaded on Mach #4 only (whole row 7)
My gosh, I hope I am clear...!!

Hi

Where did you put the macro? General Module, or sheet module? Maybe update your example file to include the macro and reattach to the post.

rylo

Ooops, sorry....It seems to work well
Macro were disabled (I am doing the test on my private laptop, and it is with Excel 2007)
I will make final tests tomorrow, but it seems to be exactly what I was looking for
Thanks a lot, and i will keep you posted for sure

OK, here is a simplified version of the file (only 1 machine)
As you can see, the 3 first rows are filled in, and the corresponding tools (Tool 1 to Tool 3) show up in the machine sheet - Great
Then 2 blank rows
Then 1 row with data, and corresponding tool shows up (Tool 4)- Still working fine
Then another 4 blank rows
Then 1 row with data, and corresponding tool shows up (Tool 5) - Still working fine
Then 14 blank rows
And now, all the remaining row properly filled in with data won't show the corresponding tool in the machine sheet......!!!

So why only 5 tools are showing up, especially when Tool 4 and Tool 5 are already separated with blank rows

Thanks in advance

OK, I've come up with a solution....It's may be not the most "elegant" one, but it works

I have updated my file with all data and machine sheets - I have now 25 tools and 15 machines
I have changed the macro to clear the cells 5 to 65 - I don't do a loop to clear cell by cell but I clear the whole range:
'clear out Tools and 0/1 cells
For i = 6 To 51 Step 5
Cells(i, 2).Resize(5, 1).ClearContents
Range(Cells(i + 3, 5), Cells(i + 3, 64)).ClearContents
'For j = 5 To 64
'Cells(i + 3, j).ClearContents
'Next j Next i

And yes, it's faster - However, I still have the loop to populate the 0/1 cells, and this takes time: I can see the 0 or 1 beeing filled in from left to right, and faster would be better!!!
However, I noticed it was filling in quicker on the last sheets than the first ones !!! And I don't understand why because macros are the same (copy/paste)
What could make it faster?

Hi

Turn of the workbook calculation while you are populating the cells, then then turn it back on again once you have populated the cells. This should speed things up.

On the problem of only bringing in the results for the months that the tool / machine combination was effective, try something like this

Machine #1!E6: =IF(OR($B$6="",SUMPRODUCT(--('Yearly to Daily'!$C$5:$C$54='Machine #1'!$B6),--('Yearly to Daily'!AA$5:AA$54='Machine #1'!$B$1))=0),0,SUMIF('Yearly to Daily'!$B$111:$B$160,$B6,'Yearly to Daily'!F$111:F$160))

What I've done is a test to see if the combination was effective for that time frame and only do the calc if it was valid.

See how that goes.

rylo

Hi
Is there a way to turn off the calculation at the beginning of the macro, and turn it back on at the end?
For the formula, what is exactly the "--"?
And I should replace my current formula by that one and keep the macro, or does it replace the macro?

I've just tried with "Calculation" set to manual in the menu Tool > Option > Calculation
Well, it's much faster...It's instantaneous actually. But what is the difference? Because with Calculation on manual, it seems to work the same (but much faster) than when it's set to Automatic Calculation
What do I miss when I have this parameter set on manual?
Thanks

Hi

You can put that switch in the macro itself, so that it turns the calc to manual, then turns it back on again when it is finished.

Application.Calculation = xlCalculationManual

Application.Calculation = xlCalculationAutomatic

rylo

Yep...this is what I did, and it works fine

Hi

Goody. Can you please go back to the original post and marked things as SOLVED.

rylo