numberchomper
New Member
- Joined
- Aug 19, 2009
- Messages
- 13
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-com
ffice:smarttags" /><st1:State w:st="on"><st1
lace w:st="on">Col</st1
lace></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-com
ffice
ffice" /><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.
![Eek! :o :o](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f631.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
![Eek! :o :o](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f631.png)
![Eek! :o :o](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f631.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Sheets("0015 Balance Sheet").Select<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Range("A1").Select<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Dim LR As Long<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
LR = Range("A" & Rows.Count).End(xlUp).Row<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
With Range("G7:G" & LR)<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
.FormulaR1C1 = "=SUMIF('0015 Trial Balance'!C1:C7,'0015 Balance Sheet'!RC[-6],'0015 Trial Balance'!C2)"<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
.Value = .Value<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
End With
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
However, now I wish to replace the existing formula with the following Excel formula:
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
=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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Can someone please help me input this formula into code? I really appreciate any and all feedback.