Assign a value to a Variable using R1C1

M

Mozzz

Board Regular
Joined
May 30, 2011
Messages
66
Can someone tell me why this doesn't work? It won't add NetAmount but just gives me the Text.

Code: 
            For i = 9 To FinalRow - 1'                Cells(i, 8).FormulaR1C1 = "=Sum(RC[-5]+RC[-4]-RC[-3]+RC[-2]-RC[-1])"   ' Adj Closing Balance
                If Cells(i, 1) < 4000 Then                                              ' Select quarter detail only if needed
                    Cells(i, 9) = ""
                    NetAmount = "=Sum(RC[-8]-RC[-3]+NetAmount)"
                    Cells(i, 11).FormulaR1C1 = "=Round(Sum((RC[-8]-RC[-3])/1000),0)" ' Calculate Variance


]

Thanks,

Mozzz
 
Last edited by a moderator:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hi Mozzz,

When you write that it "just gives you the Text", do you mean that the formula "=Round(Sum((RC[-8]-RC[-3])/1000),0)" is displayed instead of the result of evaluating that formula. If so, then change the numberformatting of that column to Number before running the code.

If that doesn't solve you problem, please post the entire procedure (not just a partial snippet).
 
Upvote 0
Jerry Sullivan said:
Hi Mozzz,

When you write that it "just gives you the Text", do you mean that the formula "=Round(Sum((RC[-8]-RC[-3])/1000),0)" is displayed instead of the result of evaluating that formula. If so, then change the numberformatting of that column to Number before running the code.

If that doesn't solve you problem, please post the entire procedure (not just a partial snippet).
Click to expand...


Sorry for the delay, I am running the macro past the code and when I hover over the variable it just shows the text, no amount. I am using the same lines to calculate data and it works. Here is the full code. My apologies, guess I should have added it from the start.

As you can see I took a different route to get this working. "NetProfit = Cells(i,8) + NetProfit" which gives a numeric answer

NetProfit = = "=Sum(RC[-8]-RC[-3])+NetProfit" gives the text result when I hover over it.

Code: 
'   Post TB Codes from Table

            For i = 9 To FinalRow - 1
'                Cells(i, 8).FormulaR1C1 = "=Sum(RC[-5]+RC[-4]-RC[-3]+RC[-2]-RC[-1])"   ' Adj Closing Balance
                If Cells(i, 1) < 4000 Then ' Select quarter detail only if needed
                    NetProfit = Cells(i, 8) + NetProfit ' adds to accumulated deficit to balance BS
                    Cells(i, 9) = ""
                    Cells(i, 11).FormulaR1C1 = "=Round(Sum((RC[-8]-RC[-3])/1000),0)" ' Calculate Variance
                Else
                    Cells(i, 9).FormulaR1C1 = "=IFERROR(Vlookup(RC[-8],PriorQtr,8,False),""0"")" '  Post Prior Qtr from Tab 5
                    Cells(i, 10).FormulaR1C1 = "=Sum(RC[-2]-RC[-1])"
                End If
                
'                Cells(i, 9).FormulaR1C1 = "=Sum(RC[-6]+RC[-4]+RC[-3]+RC[-2])"  ' get Ytd after adjustments
'                Cells(i, 10).FormulaR1C1 = "=Sum(RC[-6]-RC[-2])"        ' get Qtr Variance
'                Cells(i, 11).FormulaR1C1 = "=Sum(RC[-8]-RC[-2])"        ' get Ytd Variance
                Cells(i, 12).FormulaR1C1 = "=vlookup(RC[-11],TB_Table,2,False)"
                Cells(i, 13).FormulaR1C1 = "=vlookup(RC[-12],TB_Table,3,False)"
                Cells(i, 14).FormulaR1C1 = "=vlookup(RC[-13],TB_Table,4,False)"
                Cells(i, 15).FormulaR1C1 = "=vlookup(RC[-14],TB_Table,5,False)"
                Cells(i, 16).FormulaR1C1 = "=vlookup(RC[-15],TB_Table,6,False)"
                
                If Cells(i, 1) = 2991 Then
                    Cells(i, 9).FormulaR1C1 = "=IFERROR(Vlookup(RC[-8],PriorQtr,8,False),""0"")" '  Post Prior Qtr from Tab 5
                    Cells(i, 10).FormulaR1C1 = "=Sum(RC[-2]-RC[-1])"
                End If
'                With Range(Cells(i, 5), Cells(i, 6))
'                    .BorderAround Weight:=xlThin
'                    .Borders(xlInsideVertical).LineStyle = xlContinuous
'                End With
            Next i
 
Last edited by a moderator:
Upvote 0
Please post your entire procedure, starting with Sub.... and ending with ...End Sub. Otherwise there's no way for us to see what values have been assigned to your variables.
 
Upvote 0
OK, Thank you, As you can see this macro pulls in a template to assign groups to our trial balance. Don't think I can send you the template file. If so just tell me how. Here's the full macro. Thanks again,

Mozzz

Code: 
Sub Get_K()'
'       Updated to K
'
'    Begin Date 6-6-17 to build Q2 report from Trial Balance
'
'           Get ActiveWorkbook name


            BeginBook = ActiveWorkbook.Name
            
'   Move Trial Balance to Q Template


            Application.DisplayAlerts = False
            ActiveWorkbook.Sheets(1).Copy after:=Workbooks("K Template.xlsx").Sheets(3)
            Workbooks(BeginBook).Close
        
            
'   Save Current Active Sheet (TB)


            GetDate = Date + Time
            GetDate = Format(GetDate, "DDMMMYY hh mm")
            FN = "TB" & GetDate & ".xlsx"
            ActiveWorkbook.SaveAs Filename:="C:\Users\Dan Moser\Documents\Trial Balance\" & FN, FileFormat:=51
            
    
'   Open Table to TB Coding


            Workbooks.Open Filename:= _
            "C:\Users\Dan Moser\Documents\Templates\TB Tables ii.xlsx"
                Sheets("Cash Flow Mapping").Select
                Sheets("Cash Flow Mapping").Copy after:=Workbooks(FN).Sheets(5)
   
            Workbooks("TB Tables ii.xlsx").Close
            
'   Move To TB Sheet and prepare TB Report for coding


        Sheets(4).Activate
'      Columns(3).EntireColumn.Insert
      Rows(7).EntireRow.Insert
'      Rows(8).Clear
'   Convert Text to Number


            Columns(1).Select
            With Selection
                .TextToColumns Destination:=ActiveCell.Range("A1")
                .ColumnWidth = 16.86
                .EntireColumn.AutoFit
            End With


'   Retitle TB Columns for Pivot Tables


'            Cells(8, 3) = "OpenBal 2017"
'            Range("G8:H8").Value = Array("Adj Debit(+)", "Adj Credit(-)")
            Range("I8:P8").Value = Array("PriorQtrToDate", "CurrQtrPL", "CF_Variance", "TB CD", "TBType", "TB Desc", "CF CD", "Cash Flow Desc")
                


        
'   Get Table Dimensions


                    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
                    FinalColumn = Cells(8, Columns.Count).End(xlToLeft).Column
                    
                    For i = 2 To FinalColumn
                        Columns(i).EntireColumn.AutoFit
                    Next i
                    
'   Post TB Codes from Table


            For i = 9 To FinalRow - 1
'                Cells(i, 8).FormulaR1C1 = "=Sum(RC[-5]+RC[-4]-RC[-3]+RC[-2]-RC[-1])"   ' Adj Closing Balance
                If Cells(i, 1) < 4000 Then ' Select quarter detail only if needed
                    NetProfit = Cells(i, 8) + NetProfit ' adds to accumulated deficit to balance BS
                    Cells(i, 9) = ""
                    Cells(i, 11).FormulaR1C1 = "=Round(Sum((RC[-8]-RC[-3])/1000),0)" ' Calculate Variance
                Else
                    Cells(i, 9).FormulaR1C1 = "=IFERROR(Vlookup(RC[-8],PriorQtr,8,False),""0"")" '  Post Prior Qtr from Tab 5
                    Cells(i, 10).FormulaR1C1 = "=Sum(RC[-2]-RC[-1])"
                End If
                
'                Cells(i, 9).FormulaR1C1 = "=Sum(RC[-6]+RC[-4]+RC[-3]+RC[-2])"  ' get Ytd after adjustments
'                Cells(i, 10).FormulaR1C1 = "=Sum(RC[-6]-RC[-2])"        ' get Qtr Variance
'                Cells(i, 11).FormulaR1C1 = "=Sum(RC[-8]-RC[-2])"        ' get Ytd Variance
                Cells(i, 12).FormulaR1C1 = "=vlookup(RC[-11],TB_Table,2,False)"
                Cells(i, 13).FormulaR1C1 = "=vlookup(RC[-12],TB_Table,3,False)"
                Cells(i, 14).FormulaR1C1 = "=vlookup(RC[-13],TB_Table,4,False)"
                Cells(i, 15).FormulaR1C1 = "=vlookup(RC[-14],TB_Table,5,False)"
                Cells(i, 16).FormulaR1C1 = "=vlookup(RC[-15],TB_Table,6,False)"
                
                If Cells(i, 1) = 2991 Then
                    Cells(i, 9).FormulaR1C1 = "=IFERROR(Vlookup(RC[-8],PriorQtr,8,False),""0"")" '  Post Prior Qtr from Tab 5
                    Cells(i, 10).FormulaR1C1 = "=Sum(RC[-2]-RC[-1])"
                End If
'                With Range(Cells(i, 5), Cells(i, 6))
'                    .BorderAround Weight:=xlThin
'                    .Borders(xlInsideVertical).LineStyle = xlContinuous
'                End With
            Next i
            
'   Name Ranges for Sumif's


'            Range(Cells(8, 1), Cells(FinalRow, FinalColumn)).Name = "TBTable"
            Range("A8:A" & FinalRow).Name = "ACCT_NO"
            Range("C8:C" & FinalRow).Name = "OpenBal"
            Range("J8:J" & FinalRow).Name = "CurrQtrPL"
            Range("K8:K" & FinalRow).Name = "CF_Variance"
            Range("H8:H" & FinalRow).Name = "AdjClseBal"
            Range("L8:L" & FinalRow).Name = "TB_CD"
            Range("O8:O" & FinalRow).Name = "CF_CD"
            Range("M8:M" & FinalRow).Name = "TBType"
            
'       Post Amounts to Balance Sheet


        With Sheets("Bal Sheet")
            .Cells(8, 4).Formula = "=IFERROR(Sumif(TB_CD, ""A"",AdjClseBal),""0"")" '   Cash Available
            .Cells(9, 4).Formula = "=IFERROR(Sumif(TB_CD, ""A1"",AdjClseBal),""0"")" ' Restricted Cash
            .Cells(10, 4).Formula = "=IFERROR(Sumif(TB_CD, ""B"",AdjClseBal),""0"")"  ' Accounts Receivable, Net
            .Cells(11, 4).Formula = "=IFERROR(Sumif(TB_CD, ""C"",AdjClseBal),""0"")"  ' Costs and Est Earnings of Billings
            .Cells(12, 4).Formula = "=IFERROR(Sumif(TB_CD, ""E"",AdjClseBal),""0"")"  ' Other Current Assets
            .Cells(15, 4).Formula = "=IFERROR(Sumif(TB_CD, ""F"",AdjClseBal),""0"")"  ' Property and Equip
            .Cells(16, 4).Formula = "=IFERROR(Sumif(TB_CD, ""D"",AdjClseBal),""0"")"  ' Intangible Assets
            .Cells(17, 4).Formula = "=IFERROR(Sumif(TB_CD, ""H"",AdjClseBal),""0"")"  ' Goodwill
            .Cells(18, 4).Formula = "=IFERROR(Sumif(TB_CD, ""G"",AdjClseBal),""0"")"  ' Investments
            .Cells(23, 4).Formula = "=IFERROR(Sumif(TB_CD, ""J"",AdjClseBal)*-1,""0"")"
            .Cells(24, 4).Formula = "=IFERROR(Sumif(TB_CD, ""N"",AdjClseBal)*-1,""0"")"
            .Cells(25, 4).Formula = "=IFERROR(Sumif(TB_CD, ""K"",AdjClseBal)*-1,""0"")"
            .Cells(26, 4).Formula = "=IFERROR(Sumif(TB_CD, ""L"",AdjClseBal)*-1,""0"")"
            .Cells(27, 4).Formula = "=IFERROR(Sumif(TB_CD, ""O"",AdjClseBal)*-1,""0"")"
            .Cells(28, 4).Formula = "=IFERROR(Sumif(TB_CD, ""P"",AdjClseBal)*-1,""0"")"
            .Cells(29, 4).Formula = "=IFERROR(Sumif(TB_CD, ""Q"",AdjClseBal)*-1,""0"")"
            .Cells(30, 4).Formula = "=IFERROR(Sumif(TB_CD, ""WL"",AdjClseBal)*-1,""0"")"
            .Cells(31, 4).Formula = "=IFERROR(Sumif(TB_CD, ""YY"",AdjClseBal)*-1,""0"")"
            .Cells(32, 4).Formula = "=IFERROR(Sumif(TB_CD, ""Y"",AdjClseBal)*-1,""0"")"
            .Cells(34, 4).Formula = "=IFERROR(Sumif(TB_CD, ""S"",AdjClseBal)*-1,""0"")"
            .Cells(35, 4).Formula = "=IFERROR(Sumif(TB_CD, ""Q1"",AdjClseBal)*-1,""0"")" ' Notes Payable Related Party non current
            .Cells(36, 4).Formula = "=IFERROR(Sumif(TB_CD, ""S1"",AdjClseBal)*-1,""0"")"
            .Cells(42, 4).Formula = "=IFERROR(Sumif(TB_CD, ""T3"",AdjClseBal)*-1,""0"")"
            .Cells(49, 4).Formula = "=IFERROR(Sumif(TB_CD, ""V1"",AdjClseBal)*-1,""0"")"
            .Cells(50, 4).Formula = "=IFERROR(Sumif(TB_CD, ""V2"",AdjClseBal)*-1,""0"")"
            .Cells(51, 4).Formula = "=IFERROR(Sumif(TB_CD, ""V3"",AdjClseBal)*-1,""0"")"
            .Cells(53, 4).Formula = "=IFERROR(Sumif(TB_CD, ""U"",AdjClseBal)*-1,""0"")"
            .Cells(54, 4).Formula = "=IFERROR(Sumif(TB_CD, ""W"",AdjClseBal)*-1,""0"")"
            .Cells(55, 4).Formula = "=IFERROR(Sumif(TB_CD, ""WI"",AdjClseBal)*-1,""0"")"
            .Cells(56, 4).Formula = "=IFERROR(Sumif(TB_CD, ""U1"",AdjClseBal)*-1,""0"")"
            .Cells(57, 4).Formula = "=IFERROR(Sumif(TB_CD, ""M"",AdjClseBal)*-1,""0"")"
            .Cells(58, 4).Formula = "=IFERROR(Sumif(TB_CD, ""X"",AdjClseBal)*-1,""0"")"
        End With
'   9-22-17 Stopping point


'       Post Variance Amounts for Cash Flow


        With Sheets("Cash Flow")
            .Cells(4, 9).Formula = "=IFERROR(Sumif(TB_CD, ""A"",CF_Variance),""0"")" '   Cash Available
            .Cells(5, 9).Formula = "=IFERROR(Sumif(TB_CD, ""A1"",CF_Variance),""0"")" ' Restricted Cash
            .Cells(6, 9).Formula = "=IFERROR(Sumif(TB_CD, ""B"",CF_Variance),""0"")"  ' Accounts Receivable, Net
            .Cells(7, 9).Formula = "=IFERROR(Sumif(TB_CD, ""C"",CF_Variance),""0"")"  ' Costs and Est Earnings of Billings
            .Cells(8, 9).Formula = "=IFERROR(Sumif(TB_CD, ""E"",CF_Variance),""0"")"  ' Other Current Assets
            .Cells(11, 9).Formula = "=IFERROR(Sumif(TB_CD, ""F"",CF_Variance),""0"")"  ' Property and Equip
            .Cells(12, 9).Formula = "=IFERROR(Sumif(TB_CD, ""D"",CF_Variance),""0"")"  ' Intangible Assets
            .Cells(13, 9).Formula = "=IFERROR(Sumif(TB_CD, ""H"",CF_Variance),""0"")"  ' Goodwill
            .Cells(14, 9).Formula = "=IFERROR(Sumif(TB_CD, ""G"",CF_Variance),""0"")"  ' Investments
            .Cells(19, 9).Formula = "=IFERROR(Sumif(TB_CD, ""J"",CF_Variance),""0"")"
            .Cells(20, 9).Formula = "=IFERROR(Sumif(TB_CD, ""N"",CF_Variance),""0"")"
            .Cells(21, 9).Formula = "=IFERROR(Sumif(TB_CD, ""K"",CF_Variance),""0"")"
            .Cells(22, 9).Formula = "=IFERROR(Sumif(TB_CD, ""L"",CF_Variance),""0"")"
            .Cells(23, 9).Formula = "=IFERROR(Sumif(TB_CD, ""O"",CF_Variance),""0"")"
            .Cells(24, 9).Formula = "=IFERROR(Sumif(TB_CD, ""P"",CF_Variance),""0"")"
            .Cells(25, 9).Formula = "=IFERROR(Sumif(TB_CD, ""Q"",CF_Variance),""0"")"
            .Cells(26, 9).Formula = "=IFERROR(Sumif(TB_CD, ""WL"",CF_Variance),""0"")"
            .Cells(27, 9).Formula = "=IFERROR(Sumif(TB_CD, ""YY"",CF_Variance),""0"")"
            .Cells(28, 9).Formula = "=IFERROR(Sumif(TB_CD, ""Y"",CF_Variance),""0"")"
            .Cells(30, 9).Formula = "=IFERROR(Sumif(TB_CD, ""S"",CF_Variance),""0"")"
            .Cells(31, 9).Formula = "=IFERROR(Sumif(TB_CD, ""Q1"",CF_Variance),""0"")"
            .Cells(32, 9).Formula = "=IFERROR(Sumif(TB_CD, ""S1"",CF_Variance),""0"")"
            .Cells(38, 9).Formula = "=IFERROR(Sumif(TB_CD, ""T3"",CF_Variance),""0"")"
            .Cells(45, 9).Formula = "=IFERROR(Sumif(TB_CD, ""V1"",CF_Variance),""0"")"
            .Cells(46, 9).Formula = "=IFERROR(Sumif(TB_CD, ""V2"",CF_Variance),""0"")"
            .Cells(47, 9).Formula = "=IFERROR(Sumif(TB_CD, ""V3"",CF_Variance),""0"")"
            .Cells(49, 9).Formula = "=IFERROR(Sumif(TB_CD, ""U"",CF_Variance),""0"")"
            .Cells(50, 9).Formula = "=IFERROR(Sumif(TB_CD, ""W"",CF_Variance),""0"")"
            .Cells(51, 9).Formula = "=IFERROR(Sumif(TB_CD, ""WI"",CF_Variance),""0"")"
            .Cells(52, 9).Formula = "=IFERROR(Sumif(TB_CD, ""U1"",CF_Variance),""0"")"
            .Cells(53, 9).Formula = "=IFERROR(Sumif(TB_CD, ""M"",CF_Variance),""0"")"
            .Cells(54, 9).Formula = "=IFERROR(Sumif(TB_CD, ""X"",CF_Variance),""0"")"
        End With
'   9-22-17 Stopping point


        
'   Point Amounts to Cash Flow Tab - Sumifs for Cash Flow - Net Cash Provided from Operations


        With Sheets("Cash Flow")
            .Cells(10, 54).Formula = "=SumIf(TBType, ""B"", CF_Variance)*-1"
            .Cells(4, 4).Formula = "=Sumif(CF_CD, ""5"",CF_Variance)"
            .Cells(5, 4).Formula = "=IFERROR(Sumif(CF_CD, ""10"",CF_Variance),""0"")"
            .Cells(6, 4).Formula = "=IFERROR(Sumif(CF_CD, ""15"",CF_Variance),""0"")"
            .Cells(7, 4).Formula = "=IFERROR(Sumif(CF_CD, ""20"",CF_Variance),""0"")"
            .Cells(8, 4).Formula = "=IFERROR(Sumif(CF_CD, ""25"",CF_Variance),""0"")"
            .Cells(9, 4).Formula = "=IFERROR(Sumif(CF_CD, ""30"",CF_Variance),""0"")"
            .Cells(10, 4).Formula = "=IFERROR(Sumif(CF_CD, ""35"",CF_Variance),""0"")"
            .Cells(11, 4).Formula = "=IFERROR(Sumif(CF_CD, ""40"",CF_Variance),""0"")"
            .Cells(12, 4).Formula = "=IFERROR(Sumif(CF_CD, ""45"",CF_Variance),""0"")"
            .Cells(13, 4).Formula = "=IFERROR(Sumif(CF_CD, ""50"",CF_Variance),""0"")"
            .Cells(14, 4).Formula = "=IFERROR(Sumif(CF_CD, ""55"",CF_Variance),""0"")"
            .Cells(17, 4).Formula = "=IFERROR(Sumif(CF_CD, ""60"",CF_Variance),""0"")"
            .Cells(18, 4).Formula = "=IFERROR(Sumif(CF_CD, ""65"",CF_Variance),""0"")"
            .Cells(19, 4).Formula = "=IFERROR(Sumif(CF_CD, ""70"",CF_Variance),""0"")"
            .Cells(20, 4).Formula = "=IFERROR(Sumif(CF_CD, ""75"",CF_Variance),""0"")"
            .Cells(21, 4).Formula = "=IFERROR(Sumif(CF_CD, ""80"",CF_Variance),""0"")"  ' Billing in excess of Costs & Est earnings on uncompleted contracts
            
'       Net Cash Flows from Investing Activities


            .Cells(25, 4).Formula = "=IFERROR(Sumif(CF_CD, ""85"",CF_Variance),""0"")"
            .Cells(26, 4).Formula = "=IFERROR(Sumif(CF_CD, ""90"",CF_Variance),""0"")"
            .Cells(27, 4).Formula = "=IFERROR(Sumif(CF_CD, ""95"",CF_Variance),""0"")" ' Proceeds Investments / Restricted Cash
            
'       Net Cash Flows form Financing Activities


            .Cells(32, 4).Formula = "=IFERROR(Sumif(CF_CD, ""100"",CF_Variance),""0"")"
            .Cells(33, 4).Formula = "=IFERROR(Sumif(CF_CD, ""105"",CF_Variance),""0"")"
            .Cells(34, 4).Formula = "=IFERROR(Sumif(CF_CD, ""110"",CF_Variance),""0"")"
            .Cells(35, 4).Formula = "=IFERROR(Sumif(CF_CD, ""115"",CF_Variance),""0"")"
            .Cells(36, 4).Formula = "=IFERROR(Sumif(CF_CD, ""120"",CF_Variance),""0"")"
            .Cells(37, 4).Formula = "=IFERROR(Sumif(CF_CD, ""125"",CF_Variance),""0"")"
            .Cells(38, 4).Formula = "=IFERROR(Sumif(CF_CD, ""130"",CF_Variance),""0"")"
            .Cells(39, 4).Formula = "=IFERROR(Sumif(CF_CD, ""135"",CF_Variance),""0"")"
            .Cells(40, 4).Formula = "=IFERROR(Sumif(CF_CD, ""140"",CF_Variance),""0"")" ' Proceeds from sale of preferred stock
            
'       Non-Cash Investing and Financing Activities


            .Cells(52, 4).Formula = "=IFERROR(Sumif(CF_CD, ""145"",CF_Variance),""0"")" ' Issuance of notes payable for the purchase of fixed assets
            .Cells(53, 4).Formula = "=IFERROR(Sumif(CF_CD, ""150"",CF_Variance),""0"")"
            .Cells(54, 4).Formula = "=IFERROR(Sumif(CF_CD, ""155"",CF_Variance),""0"")"
            .Cells(55, 4).Formula = "=IFERROR(Sumif(CF_CD, ""160"",CF_Variance),""0"")"
            .Cells(56, 4).Formula = "=IFERROR(Sumif(CF_CD, ""165"",CF_Variance),""0"")"
            .Cells(57, 4).Formula = "=IFERROR(Sumif(CF_CD, ""170"",CF_Variance),""0"")"
            .Cells(58, 4).Formula = "=IFERROR(Sumif(CF_CD, ""175"",CF_Variance),""0"")"  ' Preferred shares issued -Investors incentive
            .Cells(59, 4).Formula = "=IFERROR(Sumif(CF_CD, ""180"",CF_Variance),""0"")"
            .Cells(60, 4).Formula = "=IFERROR(Sumif(CF_CD, ""185"",CF_Variance),""0"")"
            .Cells(61, 4).Formula = "=IFERROR(Sumif(CF_CD, ""190"",CF_Variance),""0"")"
            .Cells(62, 4).Formula = "=IFERROR(Sumif(CF_CD, ""195"",CF_Variance),""0"")"
            .Cells(63, 4).Formula = "=IFERROR(Sumif(CF_CD, ""200"",CF_Variance),""0"")"
            .Cells(64, 4).Formula = "=IFERROR(Sumif(CF_CD, ""205"",CF_Variance),""0"")"
            .Cells(65, 4).Formula = "=IFERROR(Sumif(CF_CD, ""210"",CF_Variance),""0"")"  ' Common Shares Reclassified from Temporary Equity
        End With
        
        
'       Ger beginning and ending Cash


        With Sheets("Cash Flow")
            .Cells(44, 4).Formula = "=IFERROR(Sumif(CF_CD, ""1"",OpenBal),""0"")"
            .Cells(45, 4).Formula = "=IFERROR(Sumif(CF_CD, ""1"",AdjClseBal),""0"")"
        End With
        
'       Post Amounts to Income Statement Quarter =====================================================================================================================


        With Sheets("Income stm")
            .Cells(8, 5).Formula = "=IFERROR(Sumif(TB_CD, ""AA"",CurrQtrPL)*-1,""0"")"
            .Cells(9, 5).Formula = "=IFERROR(Sumif(TB_CD, ""BB"",CurrQtrPL),""0"")"
            .Cells(13, 5).Formula = "=IFERROR(Sumif(TB_CD, ""DD"",CurrQtrPL),""0"")"
            .Cells(14, 5).Formula = "=IFERROR(Sumif(TB_CD, ""EE"",CurrQtrPL),""0"")"
            .Cells(15, 5).Formula = "=IFERROR(Sumif(TB_CD, ""EEE"",CurrQtrPL),""0"")"
            .Cells(16, 5).Formula = "=IFERROR(Sumif(TB_CD, ""FF"",CurrQtrPL),""0"")"
            .Cells(17, 5).Formula = "=IFERROR(Sumif(TB_CD, ""GG"",CurrQtrPL),""0"")"
            .Cells(18, 5).Formula = "=IFERROR(Sumif(TB_CD, ""AAA"",CurrQtrPL),""0"")"
            .Cells(19, 5).Formula = "=IFERROR(Sumif(TB_CD, ""KKK"",CurrQtrPL),""0"")"
            .Cells(24, 5).Formula = "=IFERROR(Sumif(TB_CD, ""JJ"",CurrQtrPL)*-1,""0"")"
            .Cells(25, 5).Formula = "=IFERROR(Sumif(TB_CD, ""E1"",CurrQtrPL)*-1,""0"")"
            .Cells(26, 5).Formula = "=IFERROR(Sumif(TB_CD, ""J1"",CurrQtrPL)*-1,""0"")"
            .Cells(27, 5).Formula = "=IFERROR(Sumif(TB_CD, ""J2"",CurrQtrPL)*-1,""0"")"
            .Cells(28, 5).Formula = "=IFERROR(Sumif(TB_CD, ""LL"",CurrQtrPL)*-1,""0"")"
            .Cells(29, 5).Formula = "=IFERROR(Sumif(TB_CD, ""KK"",CurrQtrPL)*-1,""0"")"
            .Cells(30, 5).Formula = "=IFERROR(Sumif(TB_CD, ""IE"",CurrQtrPL)*-1,""0"")"
            .Cells(31, 5).Formula = "=IFERROR(Sumif(TB_CD, ""J3"",CurrQtrPL)*-1,""0"")"
            .Cells(35, 5).Formula = "=IFERROR(Sumif(TB_CD, ""TX"",CurrQtrPL),""0"")"
            .Cells(38, 5).Formula = "=IFERROR(Sumif(ACCT_NO, ""2991"",CurrQtrPL),""0"")"
        End With
        
'       Post Amounts to Income Statement YTD
        With Sheets("Income stm")
            .Cells(8, 12).Formula = "=IFERROR(Sumif(TB_CD, ""AA"",AdjClseBal)*-1,""0"")"
            .Cells(9, 12).Formula = "=IFERROR(Sumif(TB_CD, ""BB"",AdjClseBal),""0"")"
            .Cells(13, 12).Formula = "=IFERROR(Sumif(TB_CD, ""DD"",AdjClseBal),""0"")"
            .Cells(14, 12).Formula = "=IFERROR(Sumif(TB_CD, ""EE"",AdjClseBal),""0"")"
            .Cells(15, 12).Formula = "=IFERROR(Sumif(TB_CD, ""EEE"",AdjClseBal),""0"")"
            .Cells(16, 12).Formula = "=IFERROR(Sumif(TB_CD, ""FF"",AdjClseBal),""0"")"
            .Cells(17, 12).Formula = "=IFERROR(Sumif(TB_CD, ""GG"",AdjClseBal),""0"")"
            .Cells(18, 12).Formula = "=IFERROR(Sumif(TB_CD, ""AAA"",AdjClseBal),""0"")"
            .Cells(19, 12).Formula = "=IFERROR(Sumif(TB_CD, ""KKK"",AdjClseBal),""0"")"
            .Cells(24, 12).Formula = "=IFERROR(Sumif(TB_CD, ""JJ"",AdjClseBal)*-1,""0"")"
            .Cells(25, 12).Formula = "=IFERROR(Sumif(TB_CD, ""E1"",AdjClseBal)*-1,""0"")"
            .Cells(26, 12).Formula = "=IFERROR(Sumif(TB_CD, ""J1"",AdjClseBal)*-1,""0"")"
            .Cells(27, 12).Formula = "=IFERROR(Sumif(TB_CD, ""J2"",AdjClseBal)*-1,""0"")"
            .Cells(28, 12).Formula = "=IFERROR(Sumif(TB_CD, ""LL"",AdjClseBal)*-1,""0"")"
            .Cells(29, 12).Formula = "=IFERROR(Sumif(TB_CD, ""KK"",AdjClseBal)*-1,""0"")"
            .Cells(30, 12).Formula = "=IFERROR(Sumif(TB_CD, ""IE"",AdjClseBal)*-1,""0"")"
            .Cells(31, 12).Formula = "=IFERROR(Sumif(TB_CD, ""J3"",AdjClseBal)*-1,""0"")"
            .Cells(35, 12).Formula = "=IFERROR(Sumif(TB_CD, ""TX"",AdjClseBal),""0"")"
            .Cells(38, 12).Formula = "=IFERROR(Sumif(ACCT_NO, ""2991"",CF_Variance)*-1,""0"")"
        End With
        
'        With Sheets("Income stm")
'            .Cells(8, 12).Formula = "=IFERROR(Sumif(TB_CD, ""AA"",AdjClseBal)*-1,""0"")"
'            .Cells(9, 12).Formula = "=IFERROR(Sumif(TB_CD, ""BB"",AdjClseBal),""0"")"
'            .Cells(13, 12).Formula = "=IFERROR(Sumif(TB_CD, ""DD"",AdjClseBal),""0"")"
'            .Cells(14, 12).Formula = "=IFERROR(Sumif(TB_CD, ""EE"",AdjClseBal),""0"")"
'            .Cells(15, 12).Formula = "=IFERROR(Sumif(TB_CD, ""FF"",AdjClseBal),""0"")"
'            .Cells(16, 12).Formula = "=IFERROR(Sumif(TB_CD, ""GG"",AdjClseBal),""0"")"
'            .Cells(17, 12).Formula = "=IFERROR(Sumif(TB_CD, ""AAA"",AdjClseBal),""0"")"
'            .Cells(22, 12).Formula = "=IFERROR(Sumif(TB_CD, ""JJ"",AdjClseBal)*-1,""0"")"
'            .Cells(23, 12).Formula = "=IFERROR(Sumif(TB_CD, ""E1"",AdjClseBal)*-1,""0"")"
'            .Cells(24, 12).Formula = "=IFERROR(Sumif(TB_CD, ""J1"",AdjClseBal)*-1,""0"")"
'            .Cells(25, 12).Formula = "=IFERROR(Sumif(TB_CD, ""J2"",AdjClseBal)*-1,""0"")"
'            .Cells(26, 12).Formula = "=IFERROR(Sumif(TB_CD, ""LL"",AdjClseBal)*-1,""0"")"
'            .Cells(27, 12).Formula = "=IFERROR(Sumif(TB_CD, ""KK"",AdjClseBal)*-1,""0"")"
'            .Cells(28, 12).Formula = "=IFERROR(Sumif(TB_CD, ""IE"",AdjClseBal)*-1,""0"")"
'            .Cells(29, 12).Formula = "=IFERROR(Sumif(TB_CD, ""J3"",AdjClseBal)*-1,""0"")"
'            .Cells(33, 12).Formula = "=IFERROR(Sumif(ACCT_NO, ""2991"",CF_Variance)*-1,""0"")"
'        End With
        
        '       Add Loss to Accumulated Deficit


            Sheets("Bal Sheet").Cells(58, 4) = Sheets("Bal Sheet").Cells(58, 4) + NetProfit ' Sheets("Cash Flow").Cells(2, 4)
            Sheets("Bal Sheet").Cells(58, 4).NumberFormat = "#,##0"


        
'       Turn on Display Alerts


        Application.DisplayAlerts = True


End Sub
 
Upvote 0
Hi Mozzz,

Thanks for posting the entire Sub. It sounds like you've made this work using this approach you described in Post #3 .
Code: 
NetProfit = Cells(i,8) + NetProfit

In response to your original question, there's a few problems with trying to use this statement...
Code: 
NetAmount = "=Sum(RC[-8]-RC[-3]+NetAmount)"

1. R1C1 expressions are typically entered into specific cells using the Range.FormulaR1C1 property. When doing so, Excel is able to interpret the R1C1 reference relative to the cell that holds the formula. Without having that reference cell, Excel doesn't have any way to evaluate "RC[-8]-RC[-3]"

2. Even if Excel was able to evaluate "RC[-8]-RC[-3]" (let's say it equals 33), the expression "=Sum(33+NetAmount)" is going to return a string. Since the entire expression is wrapped in double quotes, Excel just interprets this as a string and doesn't try to do any math operations inside the string.

I'd suggest you continue to use the alternate approach if it works for you. If that isn't doing something you need it to do, please explain and I'll try to help.
 
Last edited:
Upvote 0
Thank you so much for sticking with me on this and explaining that to me. Seems like the best way to learn VBA is the hard way. Just try to make it work. Understanding that the R1C1.Formula needs a cell to be utilized makes sense now.

Thanks,

Mozzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Fast VBA to Post 1000's of data points that show cell value ("Y") in another worksheet
Replies
3
Views
636
offthelip
O
S
  • Question Question
Macro for
Replies
2
Views
120
Sanderb
S
C
  • Solved
Help adding deletion of rows before sorting?
2
Replies
18
Views
1K
cspengel
C
C
  • Solved
Help with max rows of a million+
14 15 16
Replies
151
Views
8K
johnnyL
J
M
  • Question Question
Range.Find Result Changing Irrationally After Inserting Columns and Naming Cells
Replies
2
Views
573
matt767
M

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top