How do i add column data in a range using last active column? (VBA excel)
up vote
0
down vote
favorite
Screenshot of my excel spreadsheet
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!
excel vba
edited Nov 20 at 12:34
Ahmad
8,11143463
asked Nov 20 at 12:31
Bimsara Abisheka Senevirathne
1
|
show 3 more comments
up vote
0
down vote
favorite
Screenshot of my excel spreadsheet
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!
excel vba
edited Nov 20 at 12:34
Ahmad
8,11143463
asked Nov 20 at 12:31
Bimsara Abisheka Senevirathne
1
2
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 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 at 12:36
Yes, 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 at 12:40
You should explicitly define your worksheets and workbooks. Take a look at stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 at 12:41
1
AlsovStartColhas 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 at 12:45
|
show 3 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
Screenshot of my excel spreadsheet
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!
excel vba
edited Nov 20 at 12:34
Ahmad
8,11143463
asked Nov 20 at 12:31
Bimsara Abisheka Senevirathne
1
Screenshot of my excel spreadsheet
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!
excel vba
excel vba
edited Nov 20 at 12:34
Ahmad
8,11143463
asked Nov 20 at 12:31
Bimsara Abisheka Senevirathne
1
edited Nov 20 at 12:34
Ahmad
8,11143463
asked Nov 20 at 12:31
Bimsara Abisheka Senevirathne
1
edited Nov 20 at 12:34
Ahmad
8,11143463
edited Nov 20 at 12:34
Ahmad
8,11143463
edited Nov 20 at 12:34
Ahmad
8,11143463
8,11143463
asked Nov 20 at 12:31
Bimsara Abisheka Senevirathne
1
asked Nov 20 at 12:31
Bimsara Abisheka Senevirathne
1
asked Nov 20 at 12:31
Bimsara Abisheka Senevirathne
1
1
2
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 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 at 12:36
Yes, 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 at 12:40
You should explicitly define your worksheets and workbooks. Take a look at stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 at 12:41
1
AlsovStartColhas 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 at 12:45
|
show 3 more comments
2
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 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 at 12:36
Yes, 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 at 12:40
You should explicitly define your worksheets and workbooks. Take a look at stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 at 12:41
1
AlsovStartColhas 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 at 12:45
2
2
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 at 12:34
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 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 at 12:36
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 at 12:36
Yes, 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 at 12:40
Yes, 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 at 12:40
You should explicitly define your worksheets and workbooks. Take a look at stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 at 12:41
You should explicitly define your worksheets and workbooks. Take a look at stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 at 12:41
1
1
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 at 12:45
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 at 12:45
|
show 3 more comments
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53393064%2fhow-do-i-add-column-data-in-a-range-using-last-active-column-vba-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53393064%2fhow-do-i-add-column-data-in-a-range-using-last-active-column-vba-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
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 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 at 12:36
Yes, 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 at 12:40
You should explicitly define your worksheets and workbooks. Take a look at stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 at 12:41
1
Also
vStartColhas 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 at 12:45