Excel VBA: .Formula IF with variables - How to insert quotation marks?

0
votes

Thanks to mirabeau @ Mrexcel for original script.

What the original code does Sums every range in the K column and adds total to end

What I want it to do Replace the sum with a sumif that sums only the cells meeting the criterion cell, unless the criterion cell containts "Bloomingdales*". Then the entire range should be summed.

The issue I am checking if formula cell.offset(-4,-10) equals the exception. I am however having troubles mixing variables and strings and even variables and variables. Ideally, it should check if e = Store or if e = "Bloomingdales*". Either the code won't execute, or I only get it to display "e=store", where Excel can't interpret the VBA variables.

My attempt: 

Sub addups()
Dim e As Range, f As Range, g As Range, u As String, x As Range, Store As String
Set e = Columns("K").Rows(2) 'any column and/or start row you like
If e = "" Then Set e = e.End(4)
Do
Set g = e
If e.Offset(1) <> "" Then Set e = e.End(4)
u = Range(g, e).Address(0, 0)
Set f = e.Offset(1)
Set e = e.End(4)
Set x = f.Offset(-4, -10)
Store = "Bloomingdales*"
f.Formula = "=IF("& x &" = "&Store&",Sum(" & u & "),Sumif(A:A," & x.Address & ", K:K))"'Error
Loop Until e.Row = Rows.Count
End Sub

Please let me know if you need more info. Cheers!

2
excelvba

2 Answers

2
votes

To use a quotation mark in VBA, you need to escape it using another quotation.

For example Store = """Bloomingdales""" will assign a string enclosed in quotation characters.

For clarity, I tend to define a constant

Public Const vbQuote As String = """" in one of my modules. Then I can use

Store = vbQuote & "Bloomingdales" & vbQuote

which I find more readable.

0
votes

Building on @Bathsheba's advice here is another method. A couple of assumptions and a couple of error fixes.

This little sub also produces the formula without error.

Sub insForm()
Dim x As Range, f As Range
Dim Store As String, u As String, qm As String

Store = "Bloomingdales"
qm = Chr(34)
u = "G3:G10"
Set x = Columns("K").Rows(12).Offset(-4, -10)
Set f = Range("B5")

f.Formula = "=IF(" & x.Address & " = " & qm & Store & qm & ",Sum(" & u & "),Sumif(" & "C:C" & ", " & x.Address & ", " & "K:K" & "))"

End Sub