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.

 

[Fluff]

Does that make sense?

Unfortunately, not really

Your Run_BR_Query does not look at L26:L27 so changing those values will have no affect.

Also does this

Essentially E19 runs another model

mean that there is a changesheet event running on E19?

to get outputs in P28:AN28 from "Rate Analysis" which I then want stored in E16 of "Results"

So you want the values from 25 cells merged & stored into 1 cell?
Added to which the Run_BR_Query does not affect P28:AN28

 

[Beyond_avarice]

Instead of calling a separate script, why not include it as a part of the process?

Sub VBAMePlease()

Dim lCol As Long
Dim lRow As Long
Dim BR As Single
Dim i As Single
    
Application.ScreenUpdating = False
    
    
lRow = Worksheets("Results").Cells(Rows.Count, 3).End(xlUp).Row                     'Identifies how many rows of data within Column "C"
lCol = Worksheets("Rate Analysis").Cells(28, Columns.Count).End(xlToLeft).Column    'Identifies how many columns from Row 28 have data.

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

For i = 16 To lRow

'Iterate the two colum values from "Results" sheet to "Rate Analysis" Sheet
    With Worksheets("Rate Analysis")
              .Range("L26").Value = Sheets("Results").Range("C" & i).Value
              .Range("L27").Value = Sheets("Results").Range("D" & i).Value
    End With

''Call Run_BR_Query, Instead of calling this Macro why not just implant it into the script that will iterate through the "Results" data?
        For BR = 16 To 40
            With Worksheets("Rate Analysis")
                      .Range("E19").Value = Sheets("Rate Analysis").Range(Cells(26, BR).Address).Value
                      .Range("E27").Value = Sheets("Rate Analysis").Range(Cells(29, BR).Address).Value
                      .Range(Cells(30, BR).Address & ":" & Cells(41, BR).Address).Value = Sheets("Rate Analysis").Range("D30:D41").Value
            End With
        Next BR
 
'Populate results and append to Column "E" of "Results" sheet.
'Assuming that the Results are to be appended to Column "E" and iterate down each row of data.
    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value

Next I

Application.ScreenUpdating = True

End Sub

 

[VBAMePlease]

To clear up confusion, here is a line by line transcript of what I'm trying to get this to do.

Green = Variable cells for loops in "Run_Forecast" an "Run_BR_Query"

"Run_Forecast"
1. Populate 'Rate Analysis!L40 from 'Dashboard!D4
2. Populate 'Rate Analysis!L26 from 'Results!C16
3. Populate 'Rate Analysis!L27 from 'Results!D16

"Run_BR_Query"
1. Populate 'Rate Analysis!E19 from 'Rate Analysis!P26
2. Populate 'Rate Analysis!P29 from 'Rate Analysis!E27
3. Populate 'Rate Analysis!P30:P41 from 'Rate Analysis!D30:D41
4. Populate 'Rate Analysis!E19 from 'Rate Analysis!Q26
5. ^ Loop till AN26

​

4. Populate 'Results!E16 from 'Rate Analysis!P28:AN28
5. Populate 'Rate Analysis!L26 from 'Results!C17
6. Populate 'Rate Analysis!L26 from 'Results!D17

"Run_BR_Query"

​

7. Populate 'Results!E17 from 'Rate Analysis!P28:AN28
8. ^ Loop C16:D16 to C20:D20

Does this clear up confusion?

 

[VBAMePlease]

Tried - "Run-time error '9': Subscript out of range"

 

[Beyond_avarice]

@VBAMePlease, that is an awesome way to communicate the process.

1. Populate 'Rate Analysis!L40 from 'Dashboard!D4

Is this supposed to happen once or for each iteration of data?

4. Populate 'Results!E16 from 'Rate Analysis!P28:AN28
7. Populate 'Results!E17 from 'Rate Analysis!P28:AN28

Fluff and I have both asked a similar question, that is still not clear. You have Calculated results on Sheet("Rate Analysis").Range("P28:AN28").value that you indicate you want to populate each respective iteration in Column "E" of the "Results" sheet. Your calculated results("Rate Analysis" sheet) are a 1 row by 25 columns series of data; that you indicate you want to populate (eg "Results" E16, E17 etc). We are not clear if you want 25 cells of data to appear in one cell(on your "Results" sheet) or 25 cells, starting in column "E"? If you want it all in one cell, then what kind of culmination of data is it? A Sum, Count, Average, etc?

The latest script, I have provided, Populates your "Dashboard" Sheet, once. It also populates 25 calculated cells of each iteration of data within the "Results" sheet.

 

[VBAMePlease]

@VBAMePlease, that is an awesome way to communicate the process.




Is this supposed to happen once or for each iteration of data?



Fluff and I have both asked a similar question, that is still not clear. You have Calculated results on Sheet("Rate Analysis").Range("P28:AN28").value that you indicate you want to populate each respective iteration in Column "E" of the "Results" sheet. Your calculated results("Rate Analysis" sheet) are a 1 row by 25 columns series of data; that you indicate you want to populate (eg "Results" E16, E17 etc). We are not clear if you want 25 cells of data to appear in one cell(on your "Results" sheet) or 25 cells, starting in column "E"? If you want it all in one cell, then what kind of culmination of data is it? A Sum, Count, Average, etc?

The latest script, I have provided, Populates your "Dashboard" Sheet, once. It also populates 25 calculated cells of each iteration of data within the "Results" sheet.

D4 to L40 is a one-time action.

I would like P28:AN28 to populate in E16:AC16 - meaning that all 25 cells are carried over for each iteration with column E being the start point.

 

[Beyond_avarice]

Ok then, My last script should do all of that. Let's try to figure out your "Subscript out of Range" issue.

Open your editor, and walk through the script(F8). What line does this error get thrown on? Possible issues: The spelling of the referenced Sheets are not exact.

 

[VBAMePlease]

@Beyond_avarice, highlights the first row:

"Private Sub CommandButton2_Click()"

 

[Beyond_avarice]

I know you had some MsgBox with IF statements that had no end and you were attempting to "Run" separate scripts.. Hopefully the following is what you were attempting to do. It assigns the entire Macro to your CommandButton2_Click()-event. Don't give up. It looks you are almost there.

Private Sub CommandButton2_Click()

If MsgBox("Capacity Payment Structure Up-to-date?", vbQuestion + vbYesNo) <> vbYes Then
        Exit Sub
End If
 
If MsgBox("This will take significant memory and may take up to 10 minutes to compute depending upon the number of sub-markets." _
            & vbNewLine & _
             vbNewLine & _
            "Do you wish to continue?", vbQuestion + vbOKCancel) <> vbOK Then
    Exit Sub
End If
    
Dim lCol As Long
Dim lRow As Long
Dim BR As Single
Dim i As Single
          
Application.ScreenUpdating = False
    
    
lRow = Worksheets("Results").Cells(Rows.Count, 3).End(xlUp).Row                     'Identifies how many rows of data within Column "C"
lCol = Worksheets("Rate Analysis").Cells(28, Columns.Count).End(xlToLeft).Column    'Identifies how many columns from Row 28 have data.

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

For i = 16 To lRow

'Iterate the two colum values from "Results" sheet to "Rate Analysis" Sheet
    With Worksheets("Rate Analysis")
              .Range("L26").Value = Sheets("Results").Range("C" & i).Value
              .Range("L27").Value = Sheets("Results").Range("D" & i).Value
    End With

''Call Run_BR_Query, Instead of calling this Macro why not just implant it into the script that will iterate through the "Results" data?
        For BR = 16 To 40
            With Worksheets("Rate Analysis")
                      .Range("E19").Value = Sheets("Rate Analysis").Range(Cells(26, BR).Address).Value
                      .Range("E27").Value = Sheets("Rate Analysis").Range(Cells(29, BR).Address).Value
                      .Range(Cells(30, BR).Address & ":" & Cells(41, BR).Address).Value = Sheets("Rate Analysis").Range("D30:D41").Value
            End With
        Next BR
 
'Populate results and append to Column "E" of "Results" sheet.
'Assuming that the Results are to be appended to Column "E" and iterate down each row of data.
    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value

Next i
Application.ScreenUpdating = True

End Sub

 

[VBAMePlease]

I know you had some MsgBox with IF statements that had no end and you were attempting to "Run" separate scripts.. Hopefully the following is what you were attempting to do. It assigns the entire Macro to your CommandButton2_Click()-event. Don't give up. It looks you are almost there.

Just attempted this one, I noticed the changes to the Message Box - thank you!

This still however gives me the same run-time error '9 of Sub-script out of range.

Debug has this line highlighted:

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

This is right after we set lRow and lCol.

@Beyond_avarice, I owe you my life at this point for the help here. This has been a lifesaver of a resource for me and I've said it a few times but seriously, thanks for taking the time to help here man.