Error when trying to insert a SUMIF function using VBA

0
votes

I am trying to insert a SUMIF formula into a cell using VBA. VBA is not allowing my code to run as it is. Any suggestions are appreciated.

ws and na are properly set earlier on in the code. If I simply change the SUMIF formula to a random value "x" , it appears in the desired cell. The error is occurring within the SUMIF formula that I am trying to insert into a cell.

ws.Range("B" & na.Row + 2).Value = "=SUMIF(OFFSET(B1,,,ROW()-1,1),"<>#N/A"))"

The purpose of this formula is to SUM a column of numbers while ignoring any cells that contain "#N/A".

3
excelvba
Note: the formula works fine if I manually type it into a cell. The problem occurring is while trying to get VBA to insert that formula. – cpk24
is the #N/A an error from a formula or a string? – Scott Craner
I see problem with brackets: 3 times "(" and 4 times ")" – Rafał B.
The #N/A results from a VLOOKUP formula @ScottCraner – cpk24
Thanks for noticing that, it does not solve the original problem though @RafałB. – cpk24

3 Answers

1
votes

When using quotes in a formula, you need to "double up":

ws.Range("B" & na.Row + 2).Formula = "=SUMIF(OFFSET(B1,,,ROW()-1,1),""<>#N/A"")"
1
votes

You can use AGGREGATE and remove the OFFSET which is volatile

ws.Range("B" & na.Row + 2).Formula= "=AGGREGATE(9,6,B1:B" & na.Row + 1 & ")"
1
votes

Try using 'Chr(34)':

ws.Range("B" & na.Row + 2).Formula = "=SUMIF(OFFSET(B1,,,ROW()-1,1)," & Chr(34) & "<>#N/A" & Chr(34) & ")"

Edit: Deleted quotes written by mistake