• 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.

Named argument not found - R..C.. style

Hi ,

Try this :

Code: 
Sub Summarize()
    Dim lastRow&, filterCriteria$
        filterCriteria = "<=01/10/2014"
            With Sheets(1)
                lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
                ActiveWorkbook.Names.Add Name:="probe", RefersToR1C1:="=" & .Name & "!R2C2:R" & lastRow & "C2"
            End With
            With Sheets(2)
                Range("probe").AdvancedFilter xlFilterCopy, , .Range("A3"), True
                .Range("B4:B10").Value = "=SUMIFS(" & Sheets(1).Name & "!$D$3:D" & lastRow & "," & Sheets(1).Name & "!$C$3:C" & lastRow & "," & Chr(34) & filterCriteria & Chr(34) & "," & Sheets(1).Name & "!$B$3:B" & lastRow & "," & Sheets(2).Range("A4").Address(0, 0) & ")"
                .[B3] = "Quantity"
            End With
End Sub
Narayan
 
Hi Again,

I would like to ask that what is the reason of following 1004 error message:

I just changed the name of worksheets and I get this: Method 'Range' of object '_Global' failed, why?
 

Attachments

I suspect your previous sheet names did not have space character.

If so, you need modify following line from:
Code: 
ThisWorkbook.Names.Add Name:="probe", RefersToR1C1:="=" & .Name & "!R2C2:R" & lastRow & "C2"
to
Code: 
ThisWorkbook.Names.Add Name:="probe", RefersToR1C1:="='" & .Name & "'!R2C2:R" & lastRow & "C2"

You need to add those tiny single quotes before and after sheet name.
 
Hello shrivallabha,

Thank you, in the sample file the modified code is work fine as you advised.

This code is just part of my mini project and after I added new worksheets to this file where I do other calculations I have met again this problem during this macro run. Please, could you tell how is it possible?

This line is the problem again:
Code: 
 Range("probe").AdvancedFilter xlFilterCopy, , .Range("A3"), True
 
Hi ,

Could you try this ?

Code: 
Sub Summarize()
    Dim lastRow&, filterCriteria$
    filterCriteria = "<=01/14/2014"
   
    With Sheets(1)
        lastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        ThisWorkbook.Names.Add Name:="probe", RefersToR1C1:="=" & "'" & .Name & "'!R2C2:R" & lastRow & "C2"
    End With
           
    With Sheets(2)
        Range("probe").AdvancedFilter xlFilterCopy, , .Range("A3"), True
        .Range("B4:B10").Value = "=SUMIFS('" & Sheets(1).Name & "'!$D$3:D" & lastRow & ",'" & Sheets(1).Name & "'!$C$3:C" & lastRow & "," & Chr(34) & filterCriteria & Chr(34) & ",'" & Sheets(1).Name & "'!$B$3:B" & lastRow & "," & Sheets(2).Range("A4").Address(0, 0) & ")"
        .[B3] = "Quantity"
    End With
End Sub
You might have to change the date in the criteria string , since I changed it to suit my date format.

Basically , the single quote character needs to be used before and after sheet names , in every reference to sheet names , whether it is in VBA code or in Excel formulae. I have introduced the quote in the SUMIFS formula also.

Narayan
 
Villalobos,

The error, though it occurs on that specific line, comes from the line where you build the named range "probe". The error will go away once you rectify the named range VBA syntax.

You can check the names being built under
Formulas>> Defined Names >> Name Manager
and see if they are being built correctly.
 
Hi,

I would like to reopen this thread, I trust not a problem, if yes, please ignore it. If not my question would be that how should I change the code if I would like that the code sum and paste that data which are grater than 0. My target is exclude the 0 value from the result list (on sheets(2)).

Thanks in advance the time and reply!
 
You must log in or register to reply here.
Back
Top