VBA Macro in a Macro - Help

[VBAMePlease]

Okay, following macro for whatever reason seems to not be running the Macro I have embedded in it. It is only copying the values that exist in P28:AN28 before I execute the script.

So if all the values were $50, the macro would simply copy these values over. If the values were all 0, it only copies all 0's.

Here is the first macro:

If MsgBox("Capacity Payment Structure Up-to-date?", vbQuestion + vbYesNo) <> vbYes Then Run

If MsgBox("This will take significant memory and may take up to 10 minutes to compute depending upon the number of sub-markets.", vbQuestion + vbOKCancel) <> vbOK Then Run


Application.ScreenUpdating = False


lCol = Worksheets("Rate Analysis").Cells(28, Columns.Count).End(xlToLeft).Column


Worksheets("Rate Analysis").Range("L40").Value = Sheets("Dashboard").Range("D4").Value


For i = 16 To 20


    With Worksheets("Rate Analysis")
            .Range("C" & i).Copy
            Sheets("Results").Range("L26").PasteSpecial Paste:=xlPasteValues
            .Range("D" & i).Copy
            Sheets("Results").Range("L27").PasteSpecial Paste:=xlPasteValues
    End With
    
Call Run_BR_Query


    Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Copy
    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).PasteSpecial Paste:=xlPasteValues
   
Next i


Application.ScreenUpdating = True

Here is "Run_BR_Query"

Dim c As CurrencyApplication.ScreenUpdating = False
c = 15
Do Until c = 40
    c = c + 1
    Cells(26, c).Copy
    Cells(19, 5).PasteSpecial Paste:=xlPasteValues
    Cells(27, 5).Copy
    Cells(29, c).PasteSpecial Paste:=xlPasteValues
    Range(Cells(30, 4), Cells(41, 4)).Copy
    Cells(30, c).PasteSpecial Paste:=xlPasteValues
Loop
Application.ScreenUpdating = True

Am I doing something wrong with my loop in the first macro where it isn't running "Run_BR_Query"?

I know that "Run_BR_Query" works accurately. For whatever reason, it seems like the first macro isn't running this script. I can post examples if need be.

 

[jonathann3891]

This is how I call a macro from another.

If MsgBox("Capacity Payment Structure Up-to-date?", vbQuestion + vbYesNo) <> vbYes Then Run

If MsgBox("This will take significant memory and may take up to 10 minutes to compute depending upon the number of sub-markets.", vbQuestion + vbOKCancel) <> vbOK Then Run


Application.ScreenUpdating = False


lCol = Worksheets("Rate Analysis").Cells(28, Columns.Count).End(xlToLeft).Column


Worksheets("Rate Analysis").Range("L40").Value = Sheets("Dashboard").Range("D4").Value


For i = 16 To 20


    With Worksheets("Rate Analysis")
            .Range("C" & i).Copy
            Sheets("Results").Range("L26").PasteSpecial Paste:=xlPasteValues
            .Range("D" & i).Copy
            Sheets("Results").Range("L27").PasteSpecial Paste:=xlPasteValues
    End With
    
sbRun_BR_Query


    Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Copy
    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).PasteSpecial Paste:=xlPasteValues
   
Next i


Application.ScreenUpdating = True

Sub sbRun_BR_Query
Dim c As CurrencyApplication.ScreenUpdating = False
c = 15
Do Until c = 40
    c = c + 1
    Cells(26, c).Copy
    Cells(19, 5).PasteSpecial Paste:=xlPasteValues
    Cells(27, 5).Copy
    Cells(29, c).PasteSpecial Paste:=xlPasteValues
    Range(Cells(30, 4), Cells(41, 4)).Copy
    Cells(30, c).PasteSpecial Paste:=xlPasteValues
Loop
Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

First off, can we assume that you have Sub Run_BR_Query() at the beginning and End Sub at the end of the last snippet of code you posted?

If so, I would note that your main code is referencing two different worksheets... your Run_BR_Query code is referencing whatever the active sheet is... perhaps a mismatch of referenced sheets is behind your problems?

 

[VBAMePlease]

First off, can we assume that you have Sub Run_BR_Query() at the beginning and End Sub at the end of the last snippet of code you posted?

If so, I would note that your main code is referencing two different worksheets... your Run_BR_Query code is referencing whatever the active sheet is... perhaps a mismatch of referenced sheets is behind your problems?

The Run_BR_Query is referencing only data on the "Rate Analysis" sheet. It is however copying and pasting the data over, given the fact that after the script is completed P28:AN28 is still on the clipboard. Could you elaborate on having the script at the beginning and end?

 

[Fluff]

At the moment your Run_BR_Query will run 4 times & do exactly the same thing 4 times.
I suspect that this is not what you want

 

[VBAMePlease]

At the moment your Run_BR_Query will run 4 times & do exactly the same thing 4 times.
I suspect that this is not what you want

Run_BR_Query runs 25 times and does what I want it to do. It is the unnamed script above that is not performing how I want. It is not adjusting the values in L26 & L27 and apparently not running BR_Query either given the fact that the BR_Query adjust outputs given information in L26 & L27.

Misread; the unnamed Macro runs 4 times and does the exact same thing. This is what I want it to do since inside the BR_Query it should change variables in L26 & L27 that then impact values in P28:AN28.

I.e. C16 & D16 from "Results" transpose to L26 & L27 in "Rate Analysis", this then requires running a BR_Query to get outputs in P28:AN28 from "Rate Analysis" which I then want stored in E16 of "Results". Then I want to get values from C17:D17 ("Results"), transpose to L26:L27 ("Rate Analysis"), and take the outputs from P28:AN28 ("Rate Analysis") and copy to E17 ("Results").

 

[Fluff]

Your Run_BR_Query does not look at L26:L27 therefore it does the same thing each time

 

[Fluff]

Misread

 

[VBAMePlease]

Misread

Updated code to account for C17:D17 and L26:L27.

Private Sub CommandButton2_Click()If MsgBox("Capacity Payment Structure Up-to-date?", vbQuestion + vbYesNo) <> vbYes Then Run


If MsgBox("This will take significant memory and may take up to 10 minutes to compute depending upon the number of sub-markets.", vbQuestion + vbOKCancel) <> vbOK Then Run


Application.ScreenUpdating = False


lCol = Worksheets("Rate Analysis").Cells(28, Columns.Count).End(xlToLeft).Column


Worksheets("Rate Analysis").Range("L40").Value = Sheets("Dashboard").Range("D4").Value


For i = 16 To 20


    With Worksheets("Results")
            .Range("C" & i).Copy
            Sheets("Rate Analysis").Range("L26").PasteSpecial Paste:=xlPasteValues
            .Range("D" & i).Copy
            Sheets("Rate Analysis").Range("L27").PasteSpecial Paste:=xlPasteValues
    End With


Call Run_BR_Query
            
    With Worksheets("Results")
            Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Copy
            .Range("E" & i & ":" & Cells(i, lCol - 11).Address).PasteSpecial Paste:=xlPasteValues
    End With


Next i


Application.ScreenUpdating = True

I only want the Run_BR_Query to toggle the value in E19 of "Rate Analysis". L26:L27 adjusts inputs based on the Market (L26) and the scenario (L27).
Essentially E19 runs another model that calculates hourly revenues. The forecasted revenues are INDEX(MATCH,MATCH) on a different sheet in the Workbook.

Does that make sense?

 

[Beyond_avarice]

So Run_BR_Query, taking one iteration from the script I provided(all within the Sheet("Rate Analysis")):

1. Populate Cell E19 from P26.
2. Populate Cell P29 from E27
3. Populate P:30:P41 from D30:D41

The Next iteration will then:

1. Populate Cell E19 from Q26.
2. Populate Cell Q29 from E27
3. Populate Q:30:Q41 from D30:D41

You have driver cells of E19,E27 and D30:D41. This Run_BR_Query, has 25 iterations (16:40) that will populate cells P26:AN26 and P30:AN41.

After the Run_BR_Query, runs through it's 25 iterations of populating data within the Sheet("Rate Analysis"); What is to happen to these values before the next iteration, of the script I provided you, is to run? Does Sheet("Rate Analysis").Range("P28:AN28").value utilize Sheet("Rate Analysis").Range("P26:AN26 and P30:AN41").value as a means of calculating it's("P28:AN28") value before that calculation is appended to Column "E" of the "Results" sheet?