• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

getting a runtime error 9 on macro need help

R

RAM72

Member
Hi All
Using a macro but on applying macro getting runtime error9
subscript out range :mad:

this line Sheets("Summary Report ").Select in yellow highlighted.

Can anyone help

Code: 
Sub Button2_Click()

'
' TEST_SUMPRODUCT Macro
'

  Dim LRW As Integer, LRS As Integer

  Sheets("Summary Report ").Select

  LRW = Sheets("Workings_1").Range("A" & Rows.Count).End(xlUp).Row
  LRS = Range("A" & Rows.Count).End(xlUp).Row

  Range("E2").Select
  ActiveCell.FormulaR1C1 = _
  "=SUMPRODUCT(Workings_1!R2C18:R" + CStr(LRW) + "C18,--(Workings_1!R2C9:R" + CStr(LRW) + "C9='Summary Report '!RC1),--(Workings_1!R2C10:R" + CStr(LRW) + "C10='Summary Report '!RC2),--(Workings_1!R2C11:R" + CStr(LRW) + "C11='Summary Report '!RC3), --(Workings_1!R2C12:R" + CStr(LRW) + "C12='Summary Report '!RC4))"

  Range("F2").Select
  ActiveCell.FormulaR1C1 = _
  "=SUMPRODUCT(Workings_1!R2C19:R" + CStr(LRW) + "C19,--(Workings_1!R2C9:R" + CStr(LRW) + "C9='Summary Report '!RC1),--(Workings_1!R2C10:R" + CStr(LRW) + "C10='Summary Report '!RC2),--(Workings_1!R2C11:R" + CStr(LRW) + "C11='Summary Report '!RC3),--(Workings_1!R2C12:R" + CStr(LRW) + "C12='Summary Report '!RC4))"



  Range("G2").Select
  ActiveCell.FormulaR1C1 = _
  "=SUMPRODUCT(Workings_1!R2C20:R" + CStr(LRW) + "C20,--(Workings_1!R2C9:R" + CStr(LRW) + "C9='Summary Report '!RC1),--(Workings_1!R2C10:R" + CStr(LRW) + "C10='Summary Report '!RC2),--(Workings_1!R2C11:R" + CStr(LRW) + "C11='Summary Report '!RC3),--(Workings_1!R2C12:R" + CStr(LRW) + "C12='Summary Report '!RC4))"
Range("H2").Select
  ActiveCell.FormulaR1C1 = _
  "=SUMPRODUCT(Workings_1!R2C27:R" + CStr(LRW) + "C27,--(Workings_1!R2C9:R" + CStr(LRW) + "C9='Summary Report '!RC1),--(Workings_1!R2C10:R" + CStr(LRW) + "C10='Summary Report '!RC2),--(Workings_1!R2C11:R" + CStr(LRW) + "C11='Summary Report '!RC3),--(Workings_1!R2C12:R" + CStr(LRW) + "C12='Summary Report '!RC4))"



  Range("E2:H2").Select
  Selection.AutoFill Destination:=Range("E2:H" + CStr(LRS))
  Range("E2:H" + CStr(LRS + 1)).Select
Range("E2:H" & LRW).Value = Range("E2:H" & LRW).Value


End Sub
 

Attachments

  • testsumproduct_2.xlsm
    84.8 KB · Views: 3
Hi !

Just by reading help of this error, that means bad spelling :
"Summary Report " is not "Summary Report" ‼

(Found by a 13 years old teenager !)
​
 
Hi ,

To add to what Marc has posted , the easiest way to avoid such typos is to go to the worksheet , double-click on the tab name and copy - paste it in the code ; this way all characters , whether leading or trailing spaces or other special characters will be faithfully transferred.

Narayan
 
Also note that the formulas you are using assume there is a space at the end of the sheet name, which is not actually present. Quick fix would be to actually add a space to the sheet name.
 
Debaser said:
Also note that the formulas you are using assume there is a space at the end of the sheet name, which is not actually present. Quick fix would be to actually add a space to the sheet name.
Click to expand...

Hi All

you were right there were trailing spaces,I got dumbed :mad:with this yesterday.
Thanks for your valuable help.:):):cool:
 
Hi ,

The correct way would be to use variables to store such names. Once a variable is declared and assigned a value , thereafter , within the code , it is the variable which is being used. At any time , if the name needs to be changed , it can be done within the code at exactly one location.

Your code rewritten only to use such variables would be :
Code: 
Sub Button2_Click()
'
'  TEST_SUMPRODUCT Macro
'

    Dim LRW As Integer, LRS As Integer
    Dim Worksht As Worksheet, Reportsht As Worksheet
   
    With ThisWorkbook
        Set Worksht = .Worksheets("Workings_1")
        Set Reportsht = .Worksheets("Summary Report ")
    End With
   
    Reportsht.Select

    LRW = Worksht.Range("A" & Rows.Count).End(xlUp).Row
   
    With ActiveSheet
        LRS = .Range("A" & Rows.Count).End(xlUp).Row

        .Range("E2").Select
        ActiveCell.FormulaR1C1 = _
                            "=SUMPRODUCT(" & Worksht.Name & "!R2C18:R" + CStr(LRW) + "C18,--(" & Worksht.Name & "!R2C9:R" + CStr(LRW) + "C9='" & Reportsht.Name & "'!RC1),--(" & Worksht.Name & "!R2C10:R" + CStr(LRW) + "C10='" & Reportsht.Name & "'!RC2),--(" & Worksht.Name & "!R2C11:R" + CStr(LRW) + "C11='" & Reportsht.Name & "'!RC3), --(" & Worksht.Name & "!R2C12:R" + CStr(LRW) + "C12='" & Reportsht.Name & "'!RC4))"

        .Range("F2").Select
        ActiveCell.FormulaR1C1 = _
                            "=SUMPRODUCT(" & Worksht.Name & "!R2C19:R" + CStr(LRW) + "C19,--(" & Worksht.Name & "!R2C9:R" + CStr(LRW) + "C9='" & Reportsht.Name & "'!RC1),--(" & Worksht.Name & "!R2C10:R" + CStr(LRW) + "C10='" & Reportsht.Name & "'!RC2),--(" & Worksht.Name & "!R2C11:R" + CStr(LRW) + "C11='" & Reportsht.Name & "'!RC3),--(" & Worksht.Name & "!R2C12:R" + CStr(LRW) + "C12='" & Reportsht.Name & "'!RC4))"

        .Range("G2").Select
        ActiveCell.FormulaR1C1 = _
                            "=SUMPRODUCT(" & Worksht.Name & "!R2C20:R" + CStr(LRW) + "C20,--(" & Worksht.Name & "!R2C9:R" + CStr(LRW) + "C9='" & Reportsht.Name & "'!RC1),--(" & Worksht.Name & "!R2C10:R" + CStr(LRW) + "C10='" & Reportsht.Name & "'!RC2),--(" & Worksht.Name & "!R2C11:R" + CStr(LRW) + "C11='" & Reportsht.Name & "'!RC3),--(" & Worksht.Name & "!R2C12:R" + CStr(LRW) + "C12='" & Reportsht.Name & "'!RC4))"

        .Range("H2").Select
        ActiveCell.FormulaR1C1 = _
                            "=SUMPRODUCT(" & Worksht.Name & "!R2C27:R" + CStr(LRW) + "C27,--(" & Worksht.Name & "!R2C9:R" + CStr(LRW) + "C9='" & Reportsht.Name & "'!RC1),--(" & Worksht.Name & "!R2C10:R" + CStr(LRW) + "C10='" & Reportsht.Name & "'!RC2),--(" & Worksht.Name & "!R2C11:R" + CStr(LRW) + "C11='" & Reportsht.Name & "'!RC3),--(" & Worksht.Name & "!R2C12:R" + CStr(LRW) + "C12='" & Reportsht.Name & "'!RC4))"

        .Range("E2:H2").Select
        Selection.AutoFill Destination:=.Range("E2:H" + CStr(LRS))
        .Range("E2:H" + CStr(LRS + 1)).Select
        .Range("E2:H" & LRW).Value = .Range("E2:H" & LRW).Value
    End With
End Sub
Narayan
 
NARAYANK991 said:
Hi ,

The correct way would be to use variables to store such names. Once a variable is declared and assigned a value , thereafter , within the code , it is the variable which is being used. At any time , if the name needs to be changed , it can be done within the code at exactly one location.

Your code rewritten only to use such variables would be :
Code: 
Sub Button2_Click()
'
'  TEST_SUMPRODUCT Macro
'

    Dim LRW As Integer, LRS As Integer
    Dim Worksht As Worksheet, Reportsht As Worksheet
  
    With ThisWorkbook
        Set Worksht = .Worksheets("Workings_1")
        Set Reportsht = .Worksheets("Summary Report ")
    End With
  
    Reportsht.Select

    LRW = Worksht.Range("A" & Rows.Count).End(xlUp).Row
  
    With ActiveSheet
        LRS = .Range("A" & Rows.Count).End(xlUp).Row

        .Range("E2").Select
        ActiveCell.FormulaR1C1 = _
                            "=SUMPRODUCT(" & Worksht.Name & "!R2C18:R" + CStr(LRW) + "C18,--(" & Worksht.Name & "!R2C9:R" + CStr(LRW) + "C9='" & Reportsht.Name & "'!RC1),--(" & Worksht.Name & "!R2C10:R" + CStr(LRW) + "C10='" & Reportsht.Name & "'!RC2),--(" & Worksht.Name & "!R2C11:R" + CStr(LRW) + "C11='" & Reportsht.Name & "'!RC3), --(" & Worksht.Name & "!R2C12:R" + CStr(LRW) + "C12='" & Reportsht.Name & "'!RC4))"

        .Range("F2").Select
        ActiveCell.FormulaR1C1 = _
                            "=SUMPRODUCT(" & Worksht.Name & "!R2C19:R" + CStr(LRW) + "C19,--(" & Worksht.Name & "!R2C9:R" + CStr(LRW) + "C9='" & Reportsht.Name & "'!RC1),--(" & Worksht.Name & "!R2C10:R" + CStr(LRW) + "C10='" & Reportsht.Name & "'!RC2),--(" & Worksht.Name & "!R2C11:R" + CStr(LRW) + "C11='" & Reportsht.Name & "'!RC3),--(" & Worksht.Name & "!R2C12:R" + CStr(LRW) + "C12='" & Reportsht.Name & "'!RC4))"

        .Range("G2").Select
        ActiveCell.FormulaR1C1 = _
                            "=SUMPRODUCT(" & Worksht.Name & "!R2C20:R" + CStr(LRW) + "C20,--(" & Worksht.Name & "!R2C9:R" + CStr(LRW) + "C9='" & Reportsht.Name & "'!RC1),--(" & Worksht.Name & "!R2C10:R" + CStr(LRW) + "C10='" & Reportsht.Name & "'!RC2),--(" & Worksht.Name & "!R2C11:R" + CStr(LRW) + "C11='" & Reportsht.Name & "'!RC3),--(" & Worksht.Name & "!R2C12:R" + CStr(LRW) + "C12='" & Reportsht.Name & "'!RC4))"

        .Range("H2").Select
        ActiveCell.FormulaR1C1 = _
                            "=SUMPRODUCT(" & Worksht.Name & "!R2C27:R" + CStr(LRW) + "C27,--(" & Worksht.Name & "!R2C9:R" + CStr(LRW) + "C9='" & Reportsht.Name & "'!RC1),--(" & Worksht.Name & "!R2C10:R" + CStr(LRW) + "C10='" & Reportsht.Name & "'!RC2),--(" & Worksht.Name & "!R2C11:R" + CStr(LRW) + "C11='" & Reportsht.Name & "'!RC3),--(" & Worksht.Name & "!R2C12:R" + CStr(LRW) + "C12='" & Reportsht.Name & "'!RC4))"

        .Range("E2:H2").Select
        Selection.AutoFill Destination:=.Range("E2:H" + CStr(LRS))
        .Range("E2:H" + CStr(LRS + 1)).Select
        .Range("E2:H" & LRW).Value = .Range("E2:H" & LRW).Value
    End With
End Sub
Narayan
Click to expand...

Hi

Again thank you:awesome: for the explanation:cool: I have a try with code.:)

Still a learner.
 
You must log in or register to reply here.
Back
Top