How do i add column data in a range using last active column? (VBA excel)

Question

When i press the button it should add all the columns in row 2.

This is my code so far:

Sub Button1_Click()
    Range("b2").Select
    ActiveCell.End(xlToRight).Select
    Range("b2").Select
    ActiveCell.End(xlToRight).Select
    Dim vStartRow As Integer
    vStartRow = 2
    Dim vStartCol As Integer
    vStartCol = 2
    Dim vEndCol As Integer
    vEndCol = ActiveCell.Column
    Dim ColumnLetter As String
    ColumnLetter = Split(Cells(1, vEndCol).Address, "$")(1)
    Dim MyCol As String
    MyCol = ColumnLetter
    MsgBox (ColumnLetter)
    Cells(vEndCol + 2, 10).Formula = "=sum(" & vStartCol & vStartRow & ":"" & MyCol & "" vStartRow & "")"
    Cells(vEndCol + 2, 10).Select
End Sub

I get an error "Application-Difined or object-defined error" can someone please fix this error for me, thank you in advance!

Can you show a sample sheet with data, and at which line does the VBA debugger report the error? and why your Sub contain 2 duplicate lines at the top? — Ahmad, Nov 20 '18 at 12:34
Change your Integers to Long. There is no advantage here in using Integer and you risk overflow as row # can be greater than Integer can handle. — QHarr, Nov 20 '18 at 12:36
Yes, https://i.stack.imgur.com/b1AE4.png , it reports the error here 'Cells(vEndCol + 2, 10).Formula = "=sum(" & vStartCol & vStartRow & ":"" & MyCol & "" vStartRow & "")" , I am newbie to javascript i just copied the code in the tutorial on how to add rows using lastactiverow — Bimsara Abisheka Senevirathne, Nov 20 '18 at 12:40
You should explicitly define your worksheets and workbooks. Take a look at https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 — Luuklag, Nov 20 '18 at 12:41
Also `vStartCol` has value 2, instead of B, so your formula is now `=sum(22: & MyCol & vStartRow & )` Litterally. you should remove the `"` around your declared variables — Luuklag, Nov 20 '18 at 12:45
Welcome to SO. I think you are using too many `"`. Have you tried with `"=sum(" & vStartCol & vStartRow & ":" & MyCol & vStartRow & ")"`? — Foxfire And Burns And Burns, Nov 20 '18 at 13:03
May please try '`Dim vStartCol As String` and `vStartCol = "B"`. Finally `Cells(vEndCol + 2, 10).Formula="=sum(" & vStartCol & vStartRow & ":" & MyCol & vStartRow & ")"`. evan after this i think you using `vEndCol` in cells row position. — Ahmed AU, Nov 20 '18 at 16:31

How do i add column data in a range using last active column? (VBA excel)

0 Answers0