CODEWITHSUNDEEP
vbaexcel
sc1324
sc1324

Reputation: 600

Adding 3 columns and pasting results in a different column

I have a table in Excel and I want to add 3 of the columns and put the sum in another column of the same sheet using VBA. However, it seems like the code runs it to the very end (row 1048576) even though my data only has 2000+ rows. When I defined the table, I defined it like its from column A to column R so I don't know how to modify the code so it will only go through how many other rows there are in the data.

You can see image below that it literally ran to the bottom of the sheet, which is not what I am asking for. The 3 columns are values only like 1, 2 and 3. And why I set up data as table is because every week I get new data with different # of rows. I tried to implement different formulas but it looks like the code will run to the very end of the column instead of stopping at the end of the data. So it is clearly not an issue about the formula but the way I am setting up the data range or the table.

Sub Prem_Inc()
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("TB")
    lRow = ws.Cells(Rows.Count, "G").End(xlUp).Row

    For i = 2 To lRow
      Worksheets("TB").Cells(i, 13).Value = "=Sum(RC[-3], RC[-2], RC[-1])"
    Next i

    With Range("M2", "M" & lRow)
      .Style = "Currency"
      .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
      .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With

    With Range("P2", "R" & lRow)
      .Style = "Currency"
      .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
      .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    End With
End Sub

enter image description here

enter image description here

Upvotes: 0

Views: 186

Answers (1)

YowE3K
YowE3K

Reputation: 23974

This appears to just be Excel's normal behaviour in a table. If you enter a formula (or a formula masquerading as a Value) into a table and there is nothing in that column already, it will copy that formula into all rows for that column.

This code will avoid the issue by just writing the formula to the specific rows. (It might still have issues if row 2 is the last row of data, but I assume that won't be an issue for you.)

Sub Prem_Inc()
    Dim lRow As Long
    Dim i As Long
    With Worksheets("TB")
        lRow = .Cells(.Rows.Count, "G").End(xlUp).Row
        With .Range(.Cells(2, "M"), .Cells(lRow, "M"))
            .FormulaR1C1 = "=Sum(RC[-3], RC[-2], RC[-1])"
            .Style = "Currency"
            'Why set NumberFormat twice??
            .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
            .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
        End With

        With .Range(.Cells(2, "P"), .Cells(lRow, "R"))
            .Style = "Currency"
            .NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
            .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
        End With
    End With
End Sub

Upvotes: 1

Related Questions