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

[Villalobos]

Hello,

I try to use R..C.. reference style during data summarizing, but I met compile error (named argument not found).
Could somebody tell me where I made mistake?

Thank you!

 

[NARAYANK991]

Hi ,

Try this :

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

 

[Villalobos]

Thanks Narayan!

 

[Villalobos]

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?

 

[shrivallabha]

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

If so, you need modify following line from:

ThisWorkbook.Names.Add Name:="probe", RefersToR1C1:="=" & .Name & "!R2C2:R" & lastRow & "C2"

to

ThisWorkbook.Names.Add Name:="probe", RefersToR1C1:="='" & .Name & "'!R2C2:R" & lastRow & "C2"

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

 

[Villalobos]

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:

 Range("probe").AdvancedFilter xlFilterCopy, , .Range("A3"), True

 

[NARAYANK991]

Hi ,

Could you try this ?

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

 

[shrivallabha]

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.

 

[Villalobos]

Hi Narayan and shrivallabha,

Now the code is running well.
Thank you for you!

 

[Villalobos]

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!