0

I have a series of cells with values i.e.

enter image description here

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?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
flavinsky
  • 309
  • 4
  • 13
  • Why don't you just use formulas and copy them down? – SJR Mar 07 '19 at 17:19
  • I want to learn VBA! =D – flavinsky Mar 07 '19 at 19:38
  • 1
    *"I want to learn VBA! =D*" Then reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/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](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fornext-statement) to get the correct syntax of a `For` loop. – Pᴇʜ Mar 08 '19 at 07: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 08 '19 at 07: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. – flavinsky Mar 10 '19 at 14:06

1 Answers1

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • 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 '19 at 17:54
  • @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 '19 at 07:22