0

I have lists of $ sale amounts in one column and then another column near it that shows if it's a "YOR" for sales or "RE" for returns and I want to sum the dollar figures of the YORs.

I have an equation that I am trying to insert into a worksheet using a macro. When you run the macro there is a cell in H16 that should fill in with the resulting total sale number

There's a variable entitled CellNumber (Long) that holds the # of items in the list. VA05NDump is the name of the worksheet with the data. 

Range("H16").FormulaR1C1 = "SUMIF((VA05NDump!R2C3:VA05NDump!R"&CellNumber&"C3),"YOR",R2C13:R"&CellNumber&"C13)

when I click out of the equation in VBA it says "compile error" expected: end of statement" and highlights what is inside this bracket: ["C3),"]

I've tried changing the "YOR" to ="YOR" and "=YOR" but that doesn't change anything.

Any help would be awesome!

Thanks!

pnuts
  • 58,317
  • 11
  • 87
  • 139
NumberJuan
  • 79
  • 1
  • 8

3 Answers3

2

Try this:

Range("H16").FormulaR1C1 = "=SUMIF((VA05NDump!R2C3:VA05NDump!R" & CellNumber & _
    "C3),""YOR"",R2C13:R" & CellNumber & "C13)"

Revisions:
1. Added = sign upfront.
2. Double quoted YOR in this part: "C3),""YOR"",R2C13:R". Read this which discusses ways of putting double quotes which applies to all strings in "" for worksheet formulas.
3. Added the missing " at the end.

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68
  • Thanks for the help, that worked perfectly. I'm surprised I missed the first "=" but it was pretty late when I was working on this. – NumberJuan May 09 '14 at 21:47
1

You are missing a closing " at the end and probably a = at the beginning of your formula.

wha'eve'
  • 3,490
  • 2
  • 11
  • 6
1

Here is a way that I have previous entered a formula into a cell. It is done by entering the formula as a string into the cell. 

'Make Formula into a string
Dim Formula As String
Formula = "=SUMIF((VA05NDump!R2C3:VA05NDump!R" & CellNumber & _
           "C3)," & chr(34) & "YOR" & chr(34) & ",R2C13:R" & CellNumber & "C13)"

'Activate the desired range
Range("H16").Activate

'Enter formula string into active cell
ActiveCell = Formula

Also "Chr(34)" can be used as ". I find this help to create a string where chr(34) & "YOR" & chr(34) = ""YOR"" so when it is entered into the cell it becomes "YOR"

josh2205
  • 264
  • 1
  • 4