View original (French)

Macro Hierarchy

Solved
padbollevrai Posted messages 58 Status Member -  
padbollevrai Posted messages 58 Status Member -
Hello,

Here’s what I need to achieve.
I have a program that retrieves values from a sheet, processes them, and transposes them into a column in another sheet.

That's great, but I need to fill in 12 columns this way!

Basically, these are 6 columns aimed at 2 (min value and max value).

BUT the Min columns can also be of type "Count" or of type "Time", in which case there is no Max column next to it.

To summarize: Columns A, C, E, G, I, K can be either Min, Count, or Time,
while Columns B, D, F, H, J, L can be either Max or Nothing.

My programming needs to adapt to each type of column, which is why I thought of a Master macro that would command Slave macros based on the name of each column, in the following (just a sketch) form: 


Sub MAIN_FILLING()

If A3 is "Count", then call Filling_Count_A, Else if A3 is "Time", then Call Filling_Time_A, Else Call Filling_Min_A

If B3 is "Max", then call Filling_Max_B, Else ""

If C3 is "Count", then call Filling_Count_C, Else if C3 is "Time", then Call Filling_Time_C, Else Call Filling_Min_C

If D3 is "Max", then call Filling_Max_D, Else ""

If E3 is "Count", then call Filling_Count_E, Else if E3 is "Time", then Call Filling_Time_E, Else Call Filling_Min_E

If F3 is "Max", then call Filling_Max_F, Else ""

If G3 is "Count", then call Filling_Count_G, Else if G3 is "Time", then Call Filling_Time_G, Else Call Filling_Min_G

If H3 is "Max", then call Filling_Max_H, Else ""

If I3 is "Count", then call Filling_Count_I, Else if I3 is "Time", then Call Filling_Time_I, Else Call Filling_Min_I

If J3 is "Max", then call Filling_Max_J, Else ""

If K3 is "Count", then call Filling_Count_K, Else if K3 is "Time", then Call Filling_Time_K, Else Call Filling_Min_K

If L3 is "Max", then call Filling_Max_L, Else ""

End Sub


It would work, but it would be quite a mess! Indeed, 24 different programs while there are only three tasks to perform...

What I would like is to have one program to fill in Counts, one for Mins, one for Maxes, and one for Times, controlled by the master macro, and which would adapt to write the information in the correct columns of course.

Is this possible, and how do we do it?

Thank you!

Configuration: Windows Vista / Chrome 34.0.1847.131

4 answers

  1.
    pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello,

    You need a function (or a procedure) that takes the column as a parameter, like this: 

    Sub Main() If [A3] = "count" Then Call Filling_Count(1) If [A3] = "time" Then Call Filling_Time(1) If [B3] = "count" Then Call Filling_Count(2) If [B3] = "time" Then Call Filling_Time(2) 'etc... End Sub Sub Filling_Count(NumCol As Integer) With columns(NumCol) 'blabla End With End Sub Sub Filling_Time(NumCol As Integer) With columns(NumCol) 'blabla End With End Sub


    You can even use a loop in your main with a select case, etc...

    Like: 
    For Col = 1 To 12 Select Case Cells(3, Col) Case "count" Call Filling_Count(Col) Case "min" Call Filling_Min(Col) Case "max" Call Filling_Max(Col) Case "Time" Call Filling_Time(Col) Case Else MsgBox "Fatal Error" End Select Next


    --
    Best regards,
    Franck
    0
  2. padbollevrai Posted messages 58 Status Member 1
     
    Wow! Excellent, thank you very much!

    Now, in my sub-macros, how do I replace the letter in the column with the variable "Col"?

    For example, if I have a Range("B" & K).Value, I just need to put Range("Col" & K)?

    Thank you.
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      The Cells syntax is suitable for this situation:
      Cells(Row, Column).Value

      Range("B3") is equal to Cells(3, 2)
      0
  3. padbollevrai Posted messages 58 Status Member 1
     
    Thank you very much.

    Last thing: You wrote 

     For Col = 1 To 12 Select Case Cells(3, Col) Case "count" Call Filling_Count(Col) Case "min" Call Filling_Min(Col) Case "max" Call Filling_Max(Col) Case "Time" Call Filling_Time(Col) Case Else MsgBox "Fatal Error" End Select Next


    However, instead of "Fatal Error" in the case of "case else", I would like it to move on to the next value. So I put a Next instead of MsgBox "Fatal Error", but that creates a duplicate Next... and it gives me a compilation error.

    Should I simply leave it empty or is there something special to do?

    Another issue: In one of my sub-macros, I get a compilation error stating "Existing declaration in current scope" on "Dim Col as Integer". I declare this variable only once; I do not understand this error, especially since the other sub-macros located higher up in the code did not trigger this error.

    Thank you
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      You don't put anything in the Case Else.

      Existing declaration in the current scope
      look carefully, you must have a variable Col declared twice.
      Replace with Column....
      0
  4. padbollevrai Posted messages 58 Status Member 1
     
    I managed to get through, everything is okay!

    I was actually putting "Dim Col as Integer" in the Main AND in each macro.

    Thank you very much!
    0