Help with inserting Excel formula into VBA code

[numberchomper]

I found the following code on this site that does exactly what I want. It counts the number of rows in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:State w:st="on"><st1lace w:st="on">Col</st1lace></st1:State> A and then copies the value of the formula in Col G starting at G7 and continuing to the last row. Perfect!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Sheets("0015 Balance Sheet").Select<o></o>
Range("A1").Select<o></o>
Dim LR As Long<o></o>
LR = Range("A" & Rows.Count).End(xlUp).Row<o></o>
With Range("G7:G" & LR)<o></o>
.FormulaR1C1 = "=SUMIF('0015 Trial Balance'!C1:C7,'0015 Balance Sheet'!RC[-6],'0015 Trial Balance'!C2)"<o></o>
.Value = .Value<o></o>
End With
<o></o>
However, now I wish to replace the existing formula with the following Excel formula:
<o></o>
=IF((SUMIF(‘0015 Trial Balance’!$A:$A,’0015 Income Statement’!A7,’0015 Trial Balance’!$D:$D)-‘0015 Income Statement’!B7)=0,”OK”, IF(SUMIF(‘0015 Trial Balance’!$A:$A, “Net Income Before Change”,’0015 Trial Balance’!$D:$D)-b7=0,”OK”,”????”))
<o></o>
Excel will not let me record a macro using this formula and when I input directly into the code, I get a “Syntax Error”. I even tried converting it to R1C1 format and received the same error.
<o></o>
Can someone please help me input this formula into code? I really appreciate any and all feedback.

 

[phxsportz]

Try this

=IF((SUMIF('0015 Trial Balance'!$A:$A,'0015 Income Statement'!A7,'0015 Trial Balance'!$D:$D)-'0015 Income Statement'!B7)=0,"OK", IF(SUMIF('0015 Trial Balance'!$A:$A, "Net Income Before Change",'0015 Trial Balance'!$D:$D)-B7=0,"OK","????"))

Your single and double quotes were the wrong characters... ie

" does not equal ”

 

[numberchomper]

Thanks for your response!

I made the change to the single and double quotes. Now I get a both a syntax error and a "end of statement expected" message at the first "OK" in the formula.

Any other suggestions?

 

[phxsportz]

Sounds like you still have a bad double or single quote in there... you should be able to just cut and paste the following...

=IF((SUMIF('0015 Trial Balance'!$A:$A,'0015 Income Statement'!A7,'0015 Trial Balance'!$D:$D)-'0015 Income Statement'!B7)=0,"OK", IF(SUMIF('0015 Trial Balance'!$A:$A, "Net Income Before Change",'0015 Trial Balance'!$D:$D)-B7=0,"OK","????"))

 

[numberchomper]

Thanks again for the response!

I have cut and pasted your formula into the code. It now appears as follows:

Sheets("0015 Balance Sheet").Select
Range("A1").Select
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("G7:G" & LR)
.FormulaR1C1 = "=IF((SUMIF('0015 Trial Balance'!$A:$A,'0015 Income Statement'!A7,'0015 Trial Balance'!$D:$D)-'0015 Income Statement'!B7)=0,"OK", IF(SUMIF('0015 Trial Balance'!$A:$A, "Net Income Before Change",'0015 Trial Balance'!$D:$D)-B7=0,"OK","????"))"
.Value = .Value
End With

I am still getting a compile error and the first "OK" is highlighted. The error I am getting is:

Compile error:
Expected: end of statement

I am sorry I am having so much trouble with this. It seems that it should be fairly straight-forward. However, I do appreciate your efforts very much!

 

[phxsportz]

=IF(SUMIF('0015 Trial Balance'!$A:$A,'0015 Income Statement'!A7,'0015 Trial Balance'!$D:$D-'0015 Income Statement'!B7)=0,"OK", IF(SUMIF('0015 Trial Balance'!$A:$A, "Net Income Before Change",'0015 Trial Balance'!$D:$D)-B7=0,"OK","????"))

=IF(SUMIF('0015 Trial Balance'!$A:$A,'0015 Income Statement'!A7,'0015 Trial Balance'!$D:$D-'0015 Income Statement'!B7)=0,"OK", IF(SUMIF('0015 Trial Balance'!$A:$A, "Net Income Before Change",'0015 Trial Balance'!$D:$D)-B7=0,"OK","????"))

 

[numberchomper]

Thanks again for the response!

I was able to find the cause of the problem in another post. It appears you have to have double quotes ("") around text items, i.e. "OK", in the formulas.

Also, I had to convert the formula to R1C1 format. That was a chore!

But everything works fine now! I would have never thought quotes could cause so much of a hassle!

Thanks again!