LR = Range("AB" & Rows.Count).End(xlUp).Row
   Range("AB7:AB" & LR).FormulaR1C1 = "=IF(RC[-9]=""Debit"",""Credit"",""Debit"")"
   
   Dim num As Long
   LR = Range("AC" & Rows.Count).End(xlUp).Row
   Range("AC7:AC" & LR).FormulaR1C1 = "=abs(RC[-9])"

This is the bottom of my code. I am trying to get the results (JUST THE VALUES) of my last 3 lines of code to be pasted into cell T7 down until the end of the data.

CodePudding user response：

I don't have an answer, but I do have a solution!

When you have Rose and columns like that, you can highlight them, including the title row, and then click "Insert Table" from the data ribbon. (Or press control T)

Besides allowing you to format the entire table at a time, call him formulas can refer to other columns by name, and a column with one formula is stored as just one formula for that column regardless of how many rows! With this, you might not have to even do any coding at all to get this done, depending on your needs. ... or, if you do need VBA for some larger part, this answer might be useless. Feel free to ignore.

In VBA, tables are called ListObjects instead of "tables", for some reason. See https://docs.microsoft.com/en-us/office/vba/api/excel.listobject

CodePudding user response：

Variable vs With Statement

R1C1-Style

Sub UsingVariableR1C1()
    Dim LR As Long: LR = Range("AC" & Rows.Count).End(xlUp).Row
    ' Declare a range variable.
    Dim rg As Range
    ' Create a reference to the range.
    Set rg = Range("AC7:AC" & LR).Offset(0, -9)
    ' Write formula.
    rg.FormulaR1C1 = "=ABS(RC[9])"
    ' Convert to values.
    rg.Value = rg.Value
End Sub

Sub UsingWithR1C1()
    Dim LR As Long: LR = Range("AC" & Rows.Count).End(xlUp).Row
    ' Create a reference to the range using the With statement.
    With Range("AC7:AC" & LR).Offset(0, -9)
        ' Write formula.
        .FormulaR1C1 = "=ABS(RC[9])"
        ' Convert to values.
        .Value = .Value
    End With
End Sub

A1-Style

Sub UsingVariableA1()
    Dim LR As Long: LR = Range("AC" & Rows.Count).End(xlUp).Row
    ' Declare a range variable.
    Dim rg As Range
    ' Create a reference to the range.
    Set rg = Range("T7:T" & LR)
    ' Write formula.
    rg.Formula = "=ABS(AC7)"
    ' or: 
    'rg.Formula = "=ABS(" & rg.Cells(1).Offset(0, 9).Address(0,0) & ")"
    ' Convert to values.
    rg.Value = rg.Value
End Sub

Sub UsingWithA1()
    Dim LR As Long: LR = Range("AC" & Rows.Count).End(xlUp).Row
    ' Create a reference to the range using the With statement.
    With Range("T7:T" & LR)
        ' Write formula.
        .Formula = "=ABS(AC7)"
        ' or:
        '.Formula = "=ABS(" & .Cells(1).Offset(0, 9).Address(0, 0) & ")"
        ' Convert to values.
        .Value = .Value
    End With
End Sub