Using sumif with a variable in a macro

[guardian4119]

Hi

I am working on the code below. I recorded a macro and got my equations (like the last line sumif equation) in R1C1 but i want them in A1. I tried rewriting the commented out line to include some of the variables, but i keep getting an error. What is wrong with the formula. Thanks

Sub macro4()
'
'
'

LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row - 1
Set LR1 = Range("A4:A" & LR)
Set LR2 = Range("D4:D" & LR)


    Range("L5").Select
    ActiveCell.FormulaR1C1 = "Oakland"

    Range("L7").Select
    ActiveCell.FormulaR1C1 = "Concord"
    
    Range("M5").Select
    'ActiveCell.Formula = "=SUMIF(" & LR1 & "," * oakland * "," & LR2 & ")"

    Range("M7").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(R4C[-12]:R11626C[-12],""*concord*"",R4C[-9]:R11626C[-9])"
 

End Sub

 

[Osvaldo Palmeiro]

Hi. See if this could help.

Sub macro4V2()
 Dim LR As Long, LR1 As Range, LR2 As Range
  LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' - 1 (?)
  Set LR1 = Range("A4:A" & LR)
  Set LR2 = Range("D4:D" & LR)
    Range("L5") = "Oakland"
    Range("L7") = "Concord"
    Range("M5") = "=SUMIF(" & LR1.Address & ",""*oakland*""," & LR2.Address & ")"
End Sub

 

[guardian4119]

Awesome! Thanks Osvaldo, it's working great!