0

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
Ashok
  • 284
  • 2
  • 5
  • 23
  • 2
    Possible duplicate of [Placing Double Quotes Within a String in VBA](https://stackoverflow.com/questions/42960548/placing-double-quotes-within-a-string-in-vba) – Pᴇʜ Jul 27 '17 at 11:36

1 Answers1

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
Shai Rado
  • 33,032
  • 6
  • 29
  • 51