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.

 

[Beyond_avarice]

I've sort of recreated the layout of your workbook and ran into a problem when I didn't have a Sheet named "Dashboards". Check to make sure that the spelling of the script matches the spelling of the Worksheets. A simple space at the beginning or end can throw this off. They have to match. The entire Macro addresses three Sheets: "Results", "Rate Analysis" and "Dashboards".

 

[VBAMePlease]

I've sort of recreated the layout of your workbook and ran into a problem when I didn't have a Sheet named "Dashboards". Check to make sure that the spelling of the script matches the spelling of the Worksheets. A simple space at the beginning or end can throw this off. They have to match. The entire Macro addresses three Sheets: "Results", "Rate Analysis" and "Dashboards".

I think that was it... Should have ran this on a Workbook with fewer queries. Pretty sure this did it though... I cannot express how thankful I am. Greatly appreciate the help here.

 

[VBAMePlease]

I've sort of recreated the layout of your workbook and ran into a problem when I didn't have a Sheet named "Dashboards". Check to make sure that the spelling of the script matches the spelling of the Worksheets. A simple space at the beginning or end can throw this off. They have to match. The entire Macro addresses three Sheets: "Results", "Rate Analysis" and "Dashboards".

I adjusted "Dashboards" to "Dashboard" and it now runs, the only problem is that it's still giving me the same strip of values for E16 as it is for E17, E18, etc.

I'm starting to think that we should include the Run_BR_Query inside of the first loop? So instead of:

For i = 16 To lRow

    With Worksheets("Rate Analysis")
              .Range("L26").Value = Sheets("Results").Range("C" & i).Value
              .Range("L27").Value = Sheets("Results").Range("D" & i).Value
    End With


        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


    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value

It'd look like this:

For i = 16 To lRow

    With Worksheets("Rate Analysis")
              .Range("L26").Value = Sheets("Results").Range("C" & i).Value
              .Range("L27").Value = Sheets("Results").Range("D" & i).Value
                    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
                    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value
    End With


Next i

 

[VBAMePlease]

@Beyond_avarice,

Just noticed that after running the full script E27 was displaying a static value and not "=Blended_Annual", I think the reason that the script is giving me the same values is because of the interaction between E27 and the cells that that populates. Would it make sense to have it written as follows:

.Range(Cells(29, BR).Address).Value = Sheets("Rate Analysis").Range("E27").Value

 

[Beyond_avarice]

The script currently has your BR script looping inside the data loop from the "Results" sheet.

Red = First Loop, Blue second Loop

[COLOR=#ff0000]For i = 16 To lRow   [/COLOR]
[COLOR=#ff0000]
    With Worksheets("Rate Analysis")
              .Range("L26").Value = Sheets("Results").Range("C" & i).Value
              .Range("L27").Value = Sheets("Results").Range("D" & i).Value
    End With[/COLOR]

 
     [COLOR=#0000ff]   For BR = 16 To 40
            With Worksheets("Rate Analysis")
                      .Range("E19").Value = Sheets("Rate Analysis").Range(Cells(26, BR).Address).Value
                      .Range([COLOR=#0000ff]Cells(29, BR).Address[/COLOR]).Value = Sheets("Rate Analysis").Range([COLOR=#0000ff]"E27")[/COLOR].Address).Value       'Modified to reflect correction
                      .Range(Cells(30, BR).Address & ":" & Cells(41, BR).Address).Value = Sheets("Rate Analysis").Range("D30:D41").Value
            End With
        Next BR[/COLOR]
 

[COLOR=#ff0000]    Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value[/COLOR]
[COLOR=#ff0000]
Next I[/COLOR]

First iteration(Data loop): Sheet("Results").Range("C16:D16").value are populated onto Sheet("Result Analysis").Range("L26:L27").value

Once that has happened. Your BR script loops it's 25 iterations

Values are then collected from Sheet("Result Analysis").Range("P28:AN28").value and populated onto Sheet("Results").Range("E16:AC16").value.

The BR loop will exit as it has completed it's task and the Data loop will move onto it's next iteration and repeat the process to the end of the data range.

So unless you have identical inputs, do one walk through of the data loop and verify that the "P28:AN28" is calculating correctly. If it is correctly, do a second walk through and again verify.

The "Data Loop" portion of this script just moves values between sheets.

The "BR" portion is populating components that are used for calculations.

"=Blended_Annual"

This would be a named range and I don't know what values it is referencing. It Appears I misread your intention for "E27", your adjustment will make "E27" populate the consecutive cells.(Looks like you're picking up on this. ) go ahead and flip the two. Make sure your "E27 is referencing "=Blended_Annual".

 

[VBAMePlease]

This would be a named range and I don't know what values it is referencing. It Appears I misread your intention for "E27", your adjustment will make "E27" populate the consecutive cells.(Looks like you're picking up on this. ) go ahead and flip the two. Make sure your "E27 is referencing "=Blended_Annual".

Got it working! Thanks. This has been a huge help for me. VBA is my weakness! I genuinely appreciate your help and can't thank you enough. Open up a consulting shop for VBA! Haha.

If I have anymore questions I will DM you given the context you now have with the situation.

Best,
VBAMePlease

 

[VBAMePlease]

@Beyond_avarice

Would there be a simple way to add in a binary switch that can kill certain rows from being calculated?

I.e.

We have the script set to run on all of the values in C16:D16 to CEndRow:DEndRow. What if in B16:BEndRow if there is a "0", then it skips running that iteration of "i". Does that make sense?
The only consideration is that if it's not a "0" value in B16:Bxx, it will be a text value. Solely off of a "0" value in the corresponding B"i" cell would the script skip those values of C"i":D"i".

My hope is that in doing ^ it could help slim up the time to compute versus the time it currently takes.

 

[VBAMePlease]

Bump.
@Beyond_avarice

 

[Beyond_avarice]

So what you want then; is for each iteration to perform an "If statement" on Column "B" and determine if it will continue the process or skip to the next iteration.

For i = 16 To lRow

[COLOR=#FF0000]    If Sheets("Results").Range("B" & i).Value <> 0 Then[/COLOR]

        '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(Cells(29, BR).Address).Value = Sheets("Rate Analysis").Range("E27").Value
                              .Range(Cells(30, BR).Address & ":" & Cells(41, BR).Address).Value = Sheets("Rate Analysis").Range("D30:D41").Value
                    End With
                Next BR
         
              
            Sheets("Results").Range("E" & i & ":" & Cells(i, lCol - 11).Address).Value = Sheets("Rate Analysis").Range("P28:" & Cells(28, lCol).Address).Value

    [COLOR=#FF0000]End If[/COLOR]

Next I

 

[VBAMePlease]

Worked, thanks. This is last question I'll ask on this, I promise. Finished the project haha.