R1C1 loop VBA has me puzzled

[cooper645]

Ok, i can normally work this stuff out but this has me puzzled.

I am trying to do some Analytic Hierarchy and set it up dynamically each time.

The code I have already is below:

Option Explicit
Option Base 1

Sub Create_Matrix()

Dim Pax As Integer, i As Integer, j As Integer, PaxArr() As Variant
Dim Col As Integer, Row As Integer

'Clear the selection
With ActiveSheet.UsedRange
    .ClearContents
    .Interior.ColorIndex = 0
End With

'Determine the number of people that need to be graded against one another
Pax = InputBox("How many people are being graded?", "Quantity of pax to grade", 10)

'Create an array suitable to hold the number of Pax
ReDim PaxArr(1 To Pax)

'Enter the names into an array for speed in coding
For i = 1 To Pax
   PaxArr(i) = InputBox("Enter Name", "Names Entry", "Test" & i)
Next i

'Enter the array into the header and first column
i = 1
For i = LBound(PaxArr) To UBound(PaxArr)
    Cells(1, i + 1).Value = PaxArr(i)
    Cells(i + 1, 1).Value = PaxArr(i)
Next i

'Enter the Black cells
For i = 2 To Pax + 1
    Cells(i, i).Interior.ColorIndex = 1
Next i

'ISSUES FROM HERE: Correctly loops through cells, INCORRECT formulas
Dim RowChange As Integer, ColChange As Integer
RowChange = 1
j = 3
For Col = 2 To Pax  'COLUMNS
    ColChange = 1
    For Row = j To Pax + 1 'ROWS
        Cells(Row, Col).Select
        Cells(Row, Col).FormulaR1C1 = "=1-R[-" & RowChange & "]C[" & ColChange & "]"
        ColChange = ColChange + 1
        RowChange = RowChange + 1
    Next Row
    j = j + 1
Next Col


End Sub

If you run it with a break point where I am having issues, you will get the layout I'm using.

The user should enter either a 1 or a 0 in the cells above the black line, and then the opposing cell on the lower half should display the opposite.

I have managed to figure out the half pyramid loop for the cells but cannot figure out how to go about changing it to work with the R1C1 formulas.
The closest I came was that the first column was correct and then the rest was not working.

If someone could take the time to run it and have a look I would greatly appreciate the help.

Coops

 

[Eric W]

Try:

For Col = 0 To Pax - 2
    For Row = Col + 1 To Pax - 1
        Cells(2, 2).Offset(Row, Col).Formula = "=1-" & Cells(2, 2).Offset(Col, Row).Address
    Next Row
Next Col

 

[cooper645]

@Eric W Thank you so much, perfect solution. You Sir, are a legend.

 

[Oaktree]

FWIW, I think the issue in your original code was that you weren't resetting RowChange within the loop.

Making the small change to:

j = 3
For col = 2 To Pax  'COLUMNS
    ColChange = 1
    RowChange = 1
    For row = j To Pax + 1 'ROWS
        Cells(row, col).Select
        Cells(row, col).FormulaR1C1 = "=1-R[-" & RowChange & "]C[" & ColChange & "]"
        ColChange = ColChange + 1
        RowChange = RowChange + 1
    Next row
    j = j + 1
Next col

gives similar results

 

[cooper645]

FWIW, I think the issue in your original code was that you weren't resetting RowChange within the loop.

Making the small change to:

j = 3
For col = 2 To Pax  'COLUMNS
    ColChange = 1
    RowChange = 1
    For row = j To Pax + 1 'ROWS
        Cells(row, col).Select
        Cells(row, col).FormulaR1C1 = "=1-R[-" & RowChange & "]C[" & ColChange & "]"
        ColChange = ColChange + 1
        RowChange = RowChange + 1
    Next row
    j = j + 1
Next col

gives similar results

Seen, thank you so much for your time also, would likely have got there in the end I hope, after looking at it for an hour trying different things and getting nowhere, I gave up posted and went to sort dinner.

Eric Ws Solution is a lot cleaner though so I will learn from that and take it forwards into future projects.