[Help] R1C1 Relative referencing for formulas

[Gribas]

Hello everybody!

I just started VBA programming and I am now developing a tool for an Internship with a sailing team. In order to make it easier for the coaches to use, they asked me to automate the spreadsheet, leaving only InputForms for them to fill in the necessary data. So far I managed to make the addition of a sailor without any problems, but when it comes to adding a second one to the Database, my relative references end up calling wrong cells from the Worksheets, making everything I did so far useless.

I already tried recording macros with relative references but the values, as said before, cause the same problem.

The basic structure of the file is:

DB (Database) - 4 columns per sailor
|
Sheet 2(Filters only useful data - 2 columns from the DB) - 2 columns per sailor
|
Sheet3(Creates single values for each timestamp using '=AVERAGEIF') - 1 column per sailor
|
|_ This third sheet gives my final values

When I run the UserForm for the second time, my sh2 and sh3 values that were supposedly from a new sailor come both from 'John' and the next sailor due to the offset not being enough for picking up the correct cells.

For the DB > sh2 process, the formulas used are:

## Deeply sorry for the mistakes, my VBA programming experience is of 48h ##

Range("CX3").End(xlToRight).Offset(0, 1).Select
        '~~> Input Position Copy Formula
        [B]Selection.FormulaR1C1 = "=IF(RC[1]="""","""",DB!R[2]C[107])"[/B]
        '~~> Input YrSa Copy Formula
        Selection.Offset(0, 1).Select
        [B]Selection.FormulaR1C1 = "=IF(DB!R[2]C[108]<0,"""",DB!R[2]C[108])"[/B]
            '~~> Copy both formulas and paste until the end of the column
            Range(Selection, Selection.Offset(0, -1)).Copy
            Range(Selection.Offset(0, -4), Selection.Offset(0, -3).End(xlDown)).Offset(0, 3).Select
            Selection.PasteSpecial

With those formulas I only have the positioning issue, since the values they present are fine. The big problem occurs at the next step:

For the sh2 > sh3 process, I created an 'index' of the YrSa values at column A so I can easily pick the values from there to create my AVERAGEIF:

Selection.FormulaR1C1 = _
                [B]"=IFERROR(AVERAGEIF('sh2'!R3C107:R187C107," & R[]C1 & ",'sh2'!R3C106:R187C106),"""")"[/B]
            Selection.Copy
            Range(Selection.Offset(0, -1), Selection.Offset(1, -1).End(xlDown)).Offset(0, 1).PasteSpecial

The 'goal formula' is this one:

=IFERROR(AVERAGEIF('sh2'!$C$3:$C$187;[COLOR=#ff0000]A3[/COLOR];'sh2'!$B$3:$B$187);"") - For the 1st Row
=IFERROR(AVERAGEIF('sh2'!$C$3:$C$187;[COLOR=#ff0000]A4[/COLOR];'sh2'!$B$3:$B$187);"") - For the 2st Row
.
.
.

So that the range stays the same but the 'index' changes as we move down the rows.



I am sorry for the long post and if somehow I forgot about something important, but I spent the whole day sitting on this chair browsing everywhere to find a solution and unfortunately I couldn't find it by myself, so I hope someone can help!

Hope to hearing from you soon!

Gabriel