Mixing
How do i add column data in a range using last active column? (VBA excel)
0
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 '18 at 12:34
Ahmad
8,20543463
asked Nov 20 '18 at 12:31
Bimsara Abisheka SenevirathneBimsara Abisheka Senevirathne
1
|
show 3 more comments
0
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 '18 at 12:34
Ahmad
8,20543463
asked Nov 20 '18 at 12:31
Bimsara Abisheka SenevirathneBimsara 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 '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, 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 stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 '18 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 '18 at 12:45
|
show 3 more comments
0
0
0
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 '18 at 12:34
Ahmad
8,20543463
asked Nov 20 '18 at 12:31
Bimsara Abisheka SenevirathneBimsara 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 '18 at 12:34
Ahmad
8,20543463
asked Nov 20 '18 at 12:31
Bimsara Abisheka SenevirathneBimsara Abisheka Senevirathne
1
edited Nov 20 '18 at 12:34
Ahmad
8,20543463
asked Nov 20 '18 at 12:31
Bimsara Abisheka SenevirathneBimsara Abisheka Senevirathne
1
edited Nov 20 '18 at 12:34
Ahmad
8,20543463
edited Nov 20 '18 at 12:34
Ahmad
8,20543463
edited Nov 20 '18 at 12:34
Ahmad
8,20543463
8,20543463
asked Nov 20 '18 at 12:31
Bimsara Abisheka SenevirathneBimsara Abisheka Senevirathne
1
asked Nov 20 '18 at 12:31
Bimsara Abisheka SenevirathneBimsara Abisheka Senevirathne
1
asked Nov 20 '18 at 12:31
Bimsara Abisheka SenevirathneBimsara 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 '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, 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 stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 '18 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 '18 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 '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, 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 stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 '18 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 '18 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 '18 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 '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
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, 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
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 '18 at 12:40
You should explicitly define your worksheets and workbooks. Take a look at stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 '18 at 12:41
You should explicitly define your worksheets and workbooks. Take a look at stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 '18 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 '18 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 '18 at 12:45
|
show 3 more comments
0
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',
autoActivateHeartbeat: false,
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
0
active
oldest
votes
0
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.
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 '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, 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 stackoverflow.com/questions/10714251/…
– Luuklag
Nov 20 '18 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 '18 at 12:45