How can I use VBA to create an =AVERAGE formula in a dynamic cell with a dynamic range?
I'm trying to write a Sub that will allow me to replace a formula for every time the macro is run.
I'm able to select the range I want using:
Range("A3").Select
Selection.End(xlDown).Select
Range("B5", ActiveCell.Offset(-1, 1)).Select
And I'm able to find the cell in which I want the formula using:
Range("A3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 1).Activate
Is there a way I can make a formula that says =AVERAGE([selected range])?
Note: I do not want to just have the value in the cell. I need to have it so there is an active formula showing the results.
excel vba
edited Nov 27 '18 at 8:06
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:14
Rigel TurdiuRigel Turdiu
103
add a comment |
I'm trying to write a Sub that will allow me to replace a formula for every time the macro is run.
I'm able to select the range I want using:
Range("A3").Select
Selection.End(xlDown).Select
Range("B5", ActiveCell.Offset(-1, 1)).Select
And I'm able to find the cell in which I want the formula using:
Range("A3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 1).Activate
Is there a way I can make a formula that says =AVERAGE([selected range])?
Note: I do not want to just have the value in the cell. I need to have it so there is an active formula showing the results.
excel vba
edited Nov 27 '18 at 8:06
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:14
Rigel TurdiuRigel Turdiu
103
2
See How to avoid using Select in Excel VBA - Stack Overflow. Once you have aRangecontaining the cells you want to average, you can just use something like=AVERAGE(" & rng.Address & ")"
– Comintern
Nov 26 '18 at 20:21
Sounds like you could create a dynamic named range and refer to that.
– QHarr
Nov 26 '18 at 20:31
add a comment |
I'm trying to write a Sub that will allow me to replace a formula for every time the macro is run.
I'm able to select the range I want using:
Range("A3").Select
Selection.End(xlDown).Select
Range("B5", ActiveCell.Offset(-1, 1)).Select
And I'm able to find the cell in which I want the formula using:
Range("A3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 1).Activate
Is there a way I can make a formula that says =AVERAGE([selected range])?
Note: I do not want to just have the value in the cell. I need to have it so there is an active formula showing the results.
excel vba
edited Nov 27 '18 at 8:06
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:14
Rigel TurdiuRigel Turdiu
103
I'm trying to write a Sub that will allow me to replace a formula for every time the macro is run.
I'm able to select the range I want using:
Range("A3").Select
Selection.End(xlDown).Select
Range("B5", ActiveCell.Offset(-1, 1)).Select
And I'm able to find the cell in which I want the formula using:
Range("A3").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 1).Activate
Is there a way I can make a formula that says =AVERAGE([selected range])?
Note: I do not want to just have the value in the cell. I need to have it so there is an active formula showing the results.
excel vba
excel vba
edited Nov 27 '18 at 8:06
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:14
Rigel TurdiuRigel Turdiu
103
edited Nov 27 '18 at 8:06
Pᴇʜ
22.8k62850
asked Nov 26 '18 at 20:14
Rigel TurdiuRigel Turdiu
103
edited Nov 27 '18 at 8:06
Pᴇʜ
22.8k62850
edited Nov 27 '18 at 8:06
Pᴇʜ
22.8k62850
edited Nov 27 '18 at 8:06
Pᴇʜ
22.8k62850
22.8k62850
asked Nov 26 '18 at 20:14
Rigel TurdiuRigel Turdiu
103
asked Nov 26 '18 at 20:14
Rigel TurdiuRigel Turdiu
103
asked Nov 26 '18 at 20:14
Rigel TurdiuRigel Turdiu
103
103
2
See How to avoid using Select in Excel VBA - Stack Overflow. Once you have aRangecontaining the cells you want to average, you can just use something like=AVERAGE(" & rng.Address & ")"
– Comintern
Nov 26 '18 at 20:21
Sounds like you could create a dynamic named range and refer to that.
– QHarr
Nov 26 '18 at 20:31
add a comment |
2
See How to avoid using Select in Excel VBA - Stack Overflow. Once you have aRangecontaining the cells you want to average, you can just use something like=AVERAGE(" & rng.Address & ")"
– Comintern
Nov 26 '18 at 20:21
Sounds like you could create a dynamic named range and refer to that.
– QHarr
Nov 26 '18 at 20:31
2
2
See How to avoid using Select in Excel VBA - Stack Overflow. Once you have a
Range containing the cells you want to average, you can just use something like =AVERAGE(" & rng.Address & ")"– Comintern
Nov 26 '18 at 20:21
See How to avoid using Select in Excel VBA - Stack Overflow. Once you have a
Range containing the cells you want to average, you can just use something like =AVERAGE(" & rng.Address & ")"– Comintern
Nov 26 '18 at 20:21
Sounds like you could create a dynamic named range and refer to that.
– QHarr
Nov 26 '18 at 20:31
Sounds like you could create a dynamic named range and refer to that.
– QHarr
Nov 26 '18 at 20:31
add a comment |
1 Answer
1
active
oldest
votes
It would seem you are trying to do something like this:
Dim lrows As Long
lrows = Range("A3").End(xlDown).Row - 1
Range("A" & lrows + 1).Formula = "=AVERAGE(B5:B" & lrows & ")"
You should avoid using SELECTing and ACTIVATEing cells in your code, it slows it down and makes it less reliable.
edited Nov 27 '18 at 8:07
Pᴇʜ
22.8k62850
answered Nov 26 '18 at 20:29
Michal RosaMichal Rosa
1,3191814
add a comment |
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
});
}
});
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%2f53488367%2fhow-can-i-use-vba-to-create-an-average-formula-in-a-dynamic-cell-with-a-dynamic%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It would seem you are trying to do something like this:
Dim lrows As Long
lrows = Range("A3").End(xlDown).Row - 1
Range("A" & lrows + 1).Formula = "=AVERAGE(B5:B" & lrows & ")"
You should avoid using SELECTing and ACTIVATEing cells in your code, it slows it down and makes it less reliable.
edited Nov 27 '18 at 8:07
Pᴇʜ
22.8k62850
answered Nov 26 '18 at 20:29
Michal RosaMichal Rosa
1,3191814
add a comment |
It would seem you are trying to do something like this:
Dim lrows As Long
lrows = Range("A3").End(xlDown).Row - 1
Range("A" & lrows + 1).Formula = "=AVERAGE(B5:B" & lrows & ")"
You should avoid using SELECTing and ACTIVATEing cells in your code, it slows it down and makes it less reliable.
edited Nov 27 '18 at 8:07
Pᴇʜ
22.8k62850
answered Nov 26 '18 at 20:29
Michal RosaMichal Rosa
1,3191814
add a comment |
It would seem you are trying to do something like this:
Dim lrows As Long
lrows = Range("A3").End(xlDown).Row - 1
Range("A" & lrows + 1).Formula = "=AVERAGE(B5:B" & lrows & ")"
You should avoid using SELECTing and ACTIVATEing cells in your code, it slows it down and makes it less reliable.
edited Nov 27 '18 at 8:07
Pᴇʜ
22.8k62850
answered Nov 26 '18 at 20:29
Michal RosaMichal Rosa
1,3191814
It would seem you are trying to do something like this:
Dim lrows As Long
lrows = Range("A3").End(xlDown).Row - 1
Range("A" & lrows + 1).Formula = "=AVERAGE(B5:B" & lrows & ")"
You should avoid using SELECTing and ACTIVATEing cells in your code, it slows it down and makes it less reliable.
edited Nov 27 '18 at 8:07
Pᴇʜ
22.8k62850
answered Nov 26 '18 at 20:29
Michal RosaMichal Rosa
1,3191814
edited Nov 27 '18 at 8:07
Pᴇʜ
22.8k62850
edited Nov 27 '18 at 8:07
Pᴇʜ
22.8k62850
edited Nov 27 '18 at 8:07
Pᴇʜ
22.8k62850
22.8k62850
answered Nov 26 '18 at 20:29
Michal RosaMichal Rosa
1,3191814
answered Nov 26 '18 at 20:29
Michal RosaMichal Rosa
1,3191814
answered Nov 26 '18 at 20:29
Michal RosaMichal Rosa
1,3191814
1,3191814
add a comment |
add a comment |
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.
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%2f53488367%2fhow-can-i-use-vba-to-create-an-average-formula-in-a-dynamic-cell-with-a-dynamic%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
See How to avoid using Select in Excel VBA - Stack Overflow. Once you have a
Rangecontaining the cells you want to average, you can just use something like=AVERAGE(" & rng.Address & ")"– Comintern
Nov 26 '18 at 20:21
Sounds like you could create a dynamic named range and refer to that.
– QHarr
Nov 26 '18 at 20:31