How can I perform an operation in excel vba for an user-defined range?2019 Community Moderator ElectionHow to avoid using Select in Excel VBAHow to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How do I iterate over a range of numbers defined by variables in Bash?Is there a way to crack the password on an Excel VBA Project?How to avoid using Select in Excel VBAExcel VBA find a range of same values in a columnExcel VBA - read cell value from codecheck is user select data from first or other columnsExcel VBA code for simple formula between cellsExcel-VBA loop ifsExcel VBA. How to count isolated number series
HP P840 HDD RAID 5 many strange drive failures
Light propagating through a sound wave
What are substitutions for coconut in curry?
How to define limit operations in general topological spaces? Are nets able to do this?
Have the tides ever turned twice on any open problem?
How does 取材で訪れた integrate into this sentence?
What should I install to correct "ld: cannot find -lgbm and -linput" so that I can compile a Rust program?
Turning a hard to access nut?
Print a physical multiplication table
Wrapping homogeneous Python objects
Is it possible to stack the damage done by the Absorb Elements spell?
What exactly term 'companion plants' means?
Comment Box for Substitution Method of Integrals
Violin - Can double stops be played when the strings are not next to each other?
Bash - pair each line of file
Is there a term for accumulated dirt on the outside of your hands and feet?
Asserting that Atheism and Theism are both faith based positions
How to get the n-th line after a grepped one?
What can I do if I am asked to learn different programming languages very frequently?
Optimising a list searching algorithm
How are passwords stolen from companies if they only store hashes?
Should I use acronyms in dialogues before telling the readers what it stands for in fiction?
Existence of a celestial body big enough for early civilization to be thought of as a second moon
Why didn't Héctor fade away after this character died in the movie Coco?
How can I perform an operation in excel vba for an user-defined range?
2019 Community Moderator ElectionHow to avoid using Select in Excel VBAHow to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How do I iterate over a range of numbers defined by variables in Bash?Is there a way to crack the password on an Excel VBA Project?How to avoid using Select in Excel VBAExcel VBA find a range of same values in a columnExcel VBA - read cell value from codecheck is user select data from first or other columnsExcel VBA code for simple formula between cellsExcel-VBA loop ifsExcel VBA. How to count isolated number series
0
I have a series of cells with values i.e.
I would like to have a for loop that is doing the average of the first value in column A (1), the adjacent value in column B (3) and the adjacent value in column C (74). I would need the user to choose this range with a msgbox.
So far I could code this, with the help from the macro recording:
Sub averager()
Dim ran As Range, average As Variant, cell1 As Variant, cell2 As Variant
Dim i As Variant
Set ran = Application.InputBox(Prompt:="Enter range values: ", Type:=8)
For i = 0 To i = 8
ran.Offset(0, 13).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-13]:RC[-11])"
average = WorksheetFunction.average(ran.Text)
Next i
End Sub
However, this code doesn't perform the loop and it returns only the first triplicate's average in the offset position I chose.
How can the loop perform the operation for all the values?
excel vba for-loop average
edited Mar 8 at 7:28
Pᴇʜ
23.9k63052
asked Mar 7 at 17:15
Ego SumEgo Sum
7518
add a comment |
0
I have a series of cells with values i.e.
I would like to have a for loop that is doing the average of the first value in column A (1), the adjacent value in column B (3) and the adjacent value in column C (74). I would need the user to choose this range with a msgbox.
So far I could code this, with the help from the macro recording:
Sub averager()
Dim ran As Range, average As Variant, cell1 As Variant, cell2 As Variant
Dim i As Variant
Set ran = Application.InputBox(Prompt:="Enter range values: ", Type:=8)
For i = 0 To i = 8
ran.Offset(0, 13).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-13]:RC[-11])"
average = WorksheetFunction.average(ran.Text)
Next i
End Sub
However, this code doesn't perform the loop and it returns only the first triplicate's average in the offset position I chose.
How can the loop perform the operation for all the values?
excel vba for-loop average
edited Mar 8 at 7:28
Pᴇʜ
23.9k63052
asked Mar 7 at 17:15
Ego SumEgo Sum
7518
Why don't you just use formulas and copy them down?
– SJR
Mar 7 at 17:19
I want to learn VBA! =D
– Ego Sum
Mar 7 at 19:38
1
"I want to learn VBA! =D" Then reading How to avoid using Select in Excel VBA and applying that technique to your code, would probably the best to start with. • Followed by reading For...Next statement to get the correct syntax of aForloop.
– Pᴇʜ
Mar 8 at 7:32
Avoid usingVariant, this is the worst type you could choose (Sometimes you cannot avoid it, but if you can use a more adequate type). Apply the mentioned things to your code and edit and update your question (it might change your question, so you probably need to ask something else then).
– Pᴇʜ
Mar 8 at 7:34
I tried to read around but I didn't manage to implement the code for my case. I was hoping someone with some good vba experience could help me.
– Ego Sum
Mar 10 at 14:06
add a comment |
0
0
0
I have a series of cells with values i.e.
I would like to have a for loop that is doing the average of the first value in column A (1), the adjacent value in column B (3) and the adjacent value in column C (74). I would need the user to choose this range with a msgbox.
So far I could code this, with the help from the macro recording:
Sub averager()
Dim ran As Range, average As Variant, cell1 As Variant, cell2 As Variant
Dim i As Variant
Set ran = Application.InputBox(Prompt:="Enter range values: ", Type:=8)
For i = 0 To i = 8
ran.Offset(0, 13).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-13]:RC[-11])"
average = WorksheetFunction.average(ran.Text)
Next i
End Sub
However, this code doesn't perform the loop and it returns only the first triplicate's average in the offset position I chose.
How can the loop perform the operation for all the values?
excel vba for-loop average
edited Mar 8 at 7:28
Pᴇʜ
23.9k63052
asked Mar 7 at 17:15
Ego SumEgo Sum
7518
I have a series of cells with values i.e.
I would like to have a for loop that is doing the average of the first value in column A (1), the adjacent value in column B (3) and the adjacent value in column C (74). I would need the user to choose this range with a msgbox.
So far I could code this, with the help from the macro recording:
Sub averager()
Dim ran As Range, average As Variant, cell1 As Variant, cell2 As Variant
Dim i As Variant
Set ran = Application.InputBox(Prompt:="Enter range values: ", Type:=8)
For i = 0 To i = 8
ran.Offset(0, 13).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-13]:RC[-11])"
average = WorksheetFunction.average(ran.Text)
Next i
End Sub
However, this code doesn't perform the loop and it returns only the first triplicate's average in the offset position I chose.
How can the loop perform the operation for all the values?
excel vba for-loop average
excel vba for-loop average
edited Mar 8 at 7:28
Pᴇʜ
23.9k63052
asked Mar 7 at 17:15
Ego SumEgo Sum
7518
edited Mar 8 at 7:28
Pᴇʜ
23.9k63052
asked Mar 7 at 17:15
Ego SumEgo Sum
7518
edited Mar 8 at 7:28
Pᴇʜ
23.9k63052
edited Mar 8 at 7:28
Pᴇʜ
23.9k63052
edited Mar 8 at 7:28
Pᴇʜ
23.9k63052
23.9k63052
asked Mar 7 at 17:15
Ego SumEgo Sum
7518
asked Mar 7 at 17:15
Ego SumEgo Sum
7518
asked Mar 7 at 17:15
Ego SumEgo Sum
7518
7518
Why don't you just use formulas and copy them down?
– SJR
Mar 7 at 17:19
I want to learn VBA! =D
– Ego Sum
Mar 7 at 19:38
1
"I want to learn VBA! =D" Then reading How to avoid using Select in Excel VBA and applying that technique to your code, would probably the best to start with. • Followed by reading For...Next statement to get the correct syntax of aForloop.
– Pᴇʜ
Mar 8 at 7:32
Avoid usingVariant, this is the worst type you could choose (Sometimes you cannot avoid it, but if you can use a more adequate type). Apply the mentioned things to your code and edit and update your question (it might change your question, so you probably need to ask something else then).
– Pᴇʜ
Mar 8 at 7:34
I tried to read around but I didn't manage to implement the code for my case. I was hoping someone with some good vba experience could help me.
– Ego Sum
Mar 10 at 14:06
add a comment |
Why don't you just use formulas and copy them down?
– SJR
Mar 7 at 17:19
I want to learn VBA! =D
– Ego Sum
Mar 7 at 19:38
1
"I want to learn VBA! =D" Then reading How to avoid using Select in Excel VBA and applying that technique to your code, would probably the best to start with. • Followed by reading For...Next statement to get the correct syntax of aForloop.
– Pᴇʜ
Mar 8 at 7:32
Avoid usingVariant, this is the worst type you could choose (Sometimes you cannot avoid it, but if you can use a more adequate type). Apply the mentioned things to your code and edit and update your question (it might change your question, so you probably need to ask something else then).
– Pᴇʜ
Mar 8 at 7:34
I tried to read around but I didn't manage to implement the code for my case. I was hoping someone with some good vba experience could help me.
– Ego Sum
Mar 10 at 14:06
Why don't you just use formulas and copy them down?
– SJR
Mar 7 at 17:19
Why don't you just use formulas and copy them down?
– SJR
Mar 7 at 17:19
I want to learn VBA! =D
– Ego Sum
Mar 7 at 19:38
I want to learn VBA! =D
– Ego Sum
Mar 7 at 19:38
1
1
"I want to learn VBA! =D" Then reading How to avoid using Select in Excel VBA and applying that technique to your code, would probably the best to start with. • Followed by reading For...Next statement to get the correct syntax of a
For loop.– Pᴇʜ
Mar 8 at 7:32
"I want to learn VBA! =D" Then reading How to avoid using Select in Excel VBA and applying that technique to your code, would probably the best to start with. • Followed by reading For...Next statement to get the correct syntax of a
For loop.– Pᴇʜ
Mar 8 at 7:32
Avoid using
Variant, this is the worst type you could choose (Sometimes you cannot avoid it, but if you can use a more adequate type). Apply the mentioned things to your code and edit and update your question (it might change your question, so you probably need to ask something else then).– Pᴇʜ
Mar 8 at 7:34
Avoid using
Variant, this is the worst type you could choose (Sometimes you cannot avoid it, but if you can use a more adequate type). Apply the mentioned things to your code and edit and update your question (it might change your question, so you probably need to ask something else then).– Pᴇʜ
Mar 8 at 7:34
I tried to read around but I didn't manage to implement the code for my case. I was hoping someone with some good vba experience could help me.
– Ego Sum
Mar 10 at 14:06
I tried to read around but I didn't manage to implement the code for my case. I was hoping someone with some good vba experience could help me.
– Ego Sum
Mar 10 at 14:06
add a comment |
1 Answer
1
active
oldest
votes
1
- Catch the error in case the user presses the Cancel button.
- You don't need a loop, you can write the formula to multiple cells at once.
Option Explicit
Public Sub Averager()
Dim ValueRange As Range
On Error Resume Next 'if user presses cancel this throws an error
Set ValueRange = Application.InputBox(Prompt:="Select range values: ", Type:=8)
On Error GoTo 0
If Not ValueRange Is Nothing Then
ValueRange.Offset(ColumnOffset:=6).Resize(ColumnSize:=1).FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-4])"
End If
End Sub
answered Mar 11 at 16:57
PᴇʜPᴇʜ
23.9k63052
Since you raised the point of VBA learning, maybe we can better handle the errors here?Resume NextandGoto 0can suppress other errors and these make debugging a challenge. OP may be using this small macro in larger context.
– Parfait
Mar 11 at 17:54
Thank you! I will learn a lot from this!
– Ego Sum
Mar 11 at 19:47
@Parfait I have no idea how theInputBoxline (which is the only one in between my error handling) could throw any other error than the one expected when pressing the cancel button. As the box validates the addresses itself it can only return a valid range object orFalsein case of Cancel (which then throws an exception because ofSet). Actally I see no issue here, can you provide further details to investigate? • Since theInputBoxeither returns an object (which requiresSet) or a booleanFalse(which does not allowSet) I see no way arround the error handling here anyway.
– Pᴇʜ
Mar 12 at 7:22
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
);
);
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%2f55049476%2fhow-can-i-perform-an-operation-in-excel-vba-for-an-user-defined-range%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
1
- Catch the error in case the user presses the Cancel button.
- You don't need a loop, you can write the formula to multiple cells at once.
Option Explicit
Public Sub Averager()
Dim ValueRange As Range
On Error Resume Next 'if user presses cancel this throws an error
Set ValueRange = Application.InputBox(Prompt:="Select range values: ", Type:=8)
On Error GoTo 0
If Not ValueRange Is Nothing Then
ValueRange.Offset(ColumnOffset:=6).Resize(ColumnSize:=1).FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-4])"
End If
End Sub
answered Mar 11 at 16:57
PᴇʜPᴇʜ
23.9k63052
Since you raised the point of VBA learning, maybe we can better handle the errors here?Resume NextandGoto 0can suppress other errors and these make debugging a challenge. OP may be using this small macro in larger context.
– Parfait
Mar 11 at 17:54
Thank you! I will learn a lot from this!
– Ego Sum
Mar 11 at 19:47
@Parfait I have no idea how theInputBoxline (which is the only one in between my error handling) could throw any other error than the one expected when pressing the cancel button. As the box validates the addresses itself it can only return a valid range object orFalsein case of Cancel (which then throws an exception because ofSet). Actally I see no issue here, can you provide further details to investigate? • Since theInputBoxeither returns an object (which requiresSet) or a booleanFalse(which does not allowSet) I see no way arround the error handling here anyway.
– Pᴇʜ
Mar 12 at 7:22
add a comment |
1
- Catch the error in case the user presses the Cancel button.
- You don't need a loop, you can write the formula to multiple cells at once.
Option Explicit
Public Sub Averager()
Dim ValueRange As Range
On Error Resume Next 'if user presses cancel this throws an error
Set ValueRange = Application.InputBox(Prompt:="Select range values: ", Type:=8)
On Error GoTo 0
If Not ValueRange Is Nothing Then
ValueRange.Offset(ColumnOffset:=6).Resize(ColumnSize:=1).FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-4])"
End If
End Sub
answered Mar 11 at 16:57
PᴇʜPᴇʜ
23.9k63052
Since you raised the point of VBA learning, maybe we can better handle the errors here?Resume NextandGoto 0can suppress other errors and these make debugging a challenge. OP may be using this small macro in larger context.
– Parfait
Mar 11 at 17:54
Thank you! I will learn a lot from this!
– Ego Sum
Mar 11 at 19:47
@Parfait I have no idea how theInputBoxline (which is the only one in between my error handling) could throw any other error than the one expected when pressing the cancel button. As the box validates the addresses itself it can only return a valid range object orFalsein case of Cancel (which then throws an exception because ofSet). Actally I see no issue here, can you provide further details to investigate? • Since theInputBoxeither returns an object (which requiresSet) or a booleanFalse(which does not allowSet) I see no way arround the error handling here anyway.
– Pᴇʜ
Mar 12 at 7:22
add a comment |
1
1
1
- Catch the error in case the user presses the Cancel button.
- You don't need a loop, you can write the formula to multiple cells at once.
Option Explicit
Public Sub Averager()
Dim ValueRange As Range
On Error Resume Next 'if user presses cancel this throws an error
Set ValueRange = Application.InputBox(Prompt:="Select range values: ", Type:=8)
On Error GoTo 0
If Not ValueRange Is Nothing Then
ValueRange.Offset(ColumnOffset:=6).Resize(ColumnSize:=1).FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-4])"
End If
End Sub
answered Mar 11 at 16:57
PᴇʜPᴇʜ
23.9k63052
- Catch the error in case the user presses the Cancel button.
- You don't need a loop, you can write the formula to multiple cells at once.
Option Explicit
Public Sub Averager()
Dim ValueRange As Range
On Error Resume Next 'if user presses cancel this throws an error
Set ValueRange = Application.InputBox(Prompt:="Select range values: ", Type:=8)
On Error GoTo 0
If Not ValueRange Is Nothing Then
ValueRange.Offset(ColumnOffset:=6).Resize(ColumnSize:=1).FormulaR1C1 = "=AVERAGE(RC[-6]:RC[-4])"
End If
End Sub
answered Mar 11 at 16:57
PᴇʜPᴇʜ
23.9k63052
answered Mar 11 at 16:57
PᴇʜPᴇʜ
23.9k63052
answered Mar 11 at 16:57
PᴇʜPᴇʜ
23.9k63052
answered Mar 11 at 16:57
PᴇʜPᴇʜ
23.9k63052
23.9k63052
Since you raised the point of VBA learning, maybe we can better handle the errors here?Resume NextandGoto 0can suppress other errors and these make debugging a challenge. OP may be using this small macro in larger context.
– Parfait
Mar 11 at 17:54
Thank you! I will learn a lot from this!
– Ego Sum
Mar 11 at 19:47
@Parfait I have no idea how theInputBoxline (which is the only one in between my error handling) could throw any other error than the one expected when pressing the cancel button. As the box validates the addresses itself it can only return a valid range object orFalsein case of Cancel (which then throws an exception because ofSet). Actally I see no issue here, can you provide further details to investigate? • Since theInputBoxeither returns an object (which requiresSet) or a booleanFalse(which does not allowSet) I see no way arround the error handling here anyway.
– Pᴇʜ
Mar 12 at 7:22
add a comment |
Since you raised the point of VBA learning, maybe we can better handle the errors here?Resume NextandGoto 0can suppress other errors and these make debugging a challenge. OP may be using this small macro in larger context.
– Parfait
Mar 11 at 17:54
Thank you! I will learn a lot from this!
– Ego Sum
Mar 11 at 19:47
@Parfait I have no idea how theInputBoxline (which is the only one in between my error handling) could throw any other error than the one expected when pressing the cancel button. As the box validates the addresses itself it can only return a valid range object orFalsein case of Cancel (which then throws an exception because ofSet). Actally I see no issue here, can you provide further details to investigate? • Since theInputBoxeither returns an object (which requiresSet) or a booleanFalse(which does not allowSet) I see no way arround the error handling here anyway.
– Pᴇʜ
Mar 12 at 7:22
Since you raised the point of VBA learning, maybe we can better handle the errors here?
Resume Next and Goto 0 can suppress other errors and these make debugging a challenge. OP may be using this small macro in larger context.– Parfait
Mar 11 at 17:54
Since you raised the point of VBA learning, maybe we can better handle the errors here?
Resume Next and Goto 0 can suppress other errors and these make debugging a challenge. OP may be using this small macro in larger context.– Parfait
Mar 11 at 17:54
Thank you! I will learn a lot from this!
– Ego Sum
Mar 11 at 19:47
Thank you! I will learn a lot from this!
– Ego Sum
Mar 11 at 19:47
@Parfait I have no idea how the
InputBox line (which is the only one in between my error handling) could throw any other error than the one expected when pressing the cancel button. As the box validates the addresses itself it can only return a valid range object or False in case of Cancel (which then throws an exception because of Set). Actally I see no issue here, can you provide further details to investigate? • Since the InputBox either returns an object (which requires Set) or a boolean False (which does not allow Set) I see no way arround the error handling here anyway.– Pᴇʜ
Mar 12 at 7:22
@Parfait I have no idea how the
InputBox line (which is the only one in between my error handling) could throw any other error than the one expected when pressing the cancel button. As the box validates the addresses itself it can only return a valid range object or False in case of Cancel (which then throws an exception because of Set). Actally I see no issue here, can you provide further details to investigate? • Since the InputBox either returns an object (which requires Set) or a boolean False (which does not allow Set) I see no way arround the error handling here anyway.– Pᴇʜ
Mar 12 at 7:22
add a comment |
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%2f55049476%2fhow-can-i-perform-an-operation-in-excel-vba-for-an-user-defined-range%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
Why don't you just use formulas and copy them down?
– SJR
Mar 7 at 17:19
I want to learn VBA! =D
– Ego Sum
Mar 7 at 19:38
1
"I want to learn VBA! =D" Then reading How to avoid using Select in Excel VBA and applying that technique to your code, would probably the best to start with. • Followed by reading For...Next statement to get the correct syntax of a
Forloop.– Pᴇʜ
Mar 8 at 7:32
Avoid using
Variant, this is the worst type you could choose (Sometimes you cannot avoid it, but if you can use a more adequate type). Apply the mentioned things to your code and edit and update your question (it might change your question, so you probably need to ask something else then).– Pᴇʜ
Mar 8 at 7:34
I tried to read around but I didn't manage to implement the code for my case. I was hoping someone with some good vba experience could help me.
– Ego Sum
Mar 10 at 14:06