VBA : Sumif function not working

Question

Hi I am writing a code for sum the data based on criteria. The below code working perfectly but it not return value. The code works on the excel like this (= SUMIF($B$1:$DC$1,p,B2:DD2). The reason is Criteria P Need double quotation.how to add the double quotation to P and any suggestion would be appreciated

Sub ashok()
 Dim LR As Long
 Dim Rg, Rg1 As Range

                ActiveSheet.Range("a1").Select
                LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
                ActiveCell.Columns("A:A").EntireColumn.Select
                Selection.NumberFormat = "0"
                ActiveSheet.Range("a1").Select
                Set Rg = Range("b1", ActiveSheet.Range("A1").End(xlToRight))
                Set Rg1 = Range("b2", ActiveSheet.Range("A2").End(xlToRight))
                Range("a1").End(xlToRight).Select

                With ActiveCell.Offset(1, 1).Resize(LR)
                .Formula = "= SumIf(" & Rg.Address(True, True) & "," & "P" & "," & Rg1.Address(False, False) & ")"
                End With
 End Sub

Possible duplicate of Placing Double Quotes Within a String in VBA — Pᴇʜ
– Pᴇʜ, Commented Jul 27, 2017 at 11:36

Shai Rado · Accepted Answer · 2017-07-27 11:41:30Z

5

The answer to your question is use Chr(34):

.Formula = "= SumIf(" & Rg.Address(True, True) & "," & Chr(34) & "P" & Chr(34) & "," & Rg1.Address(False, False) & ")"

However, you have way too much (unnecessary and should stay away from) use of Select, ActiveSheet, ActiveCell and Selection.

An example of how your code could look if you use fully qualified objects:

With Sheets("Sheet3") ' <-- replace with your sheet's name

    LR = .Cells(.Rows.Count, "A").End(xlUp).Row

    .Columns("A:A").EntireColumn.NumberFormat = "0"

    Set Rg = .Range("B1", .Range("A1").End(xlToRight)) '<-- NOT SURE this make sense !
    Set Rg1 = .Range("B2", .Range("A2").End(xlToRight)) '<-- NOT SURE this make sense !

    ' etc. etc.

End With ' closing the With

answered Jul 27, 2017 at 11:41

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

A.S.H Over a year ago

20 congrats for the 20K :)

Shai Rado Over a year ago

@A.S.H LOL, thanks :) now I have one more thing, chase the Gold Badge for VBA and Excel VBA

A.S.H Over a year ago

Yeah, so many questions out there to be closed on the spot :P

Collectives™ on Stack Overflow

VBA : Sumif function not working

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related