Ggtcf

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?

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

share|improve this question

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 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
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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

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

share|improve this question

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 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
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 | 

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

share|improve this question

edited Mar 8 at 7:28

Pᴇʜ

23.9k63052

asked Mar 7 at 17:15

Ego SumEgo Sum

7518

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

share|improve this question

edited Mar 8 at 7:28

Pᴇʜ

23.9k63052

asked Mar 7 at 17:15

Ego SumEgo Sum

7518

share|improve this question

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

asked Mar 7 at 17:15

Ego SumEgo Sum

7518

asked Mar 7 at 17:15

Ego SumEgo Sum

7518

1 Answer
1

active

oldest

votes

1

1. Catch the error in case the user presses the Cancel button.


2. 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

share|improve this answer

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 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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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 | 

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

Name

Email

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

Name

Email

Required, but never shown

1

1. Catch the error in case the user presses the Cancel button.


2. 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

share|improve this answer

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 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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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 | 

1

1. Catch the error in case the user presses the Cancel button.


2. 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

share|improve this answer

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 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
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @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 | 

1. Catch the error in case the user presses the Cancel button.


2. 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

share|improve this answer

answered Mar 11 at 16:57

PᴇʜPᴇʜ

23.9k63052

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

share|improve this answer

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