Excel VBA transposition problem

Transposition of Formulae , Excel , VBA Townsville, Australia
  • 11 years ago

    I have a 'Participant List ' sheet with 23 'Units' across columns and 100 'Participants Names' down rows; and an 'Assessment' sheet laid out the same way. A 'Report' sheet is laid out with 'Units' in column A and 'Competent/Not Yet Competent' down Column B. There is an A4 size area for each participant going down this sheet. Each area picks up the values from the Participant and Assessment sheets using the following formula: ie the formula in D1 is =IF('Participant List'!F1="","",IF(Assessment!B1="C","Competent","Not Yet Competent")) the formula in D2 is =IF('Participant List'!G1="","",IF(Assessment!C1="C","Competent","Not Yet Competent"))

    I have to modify this formula slightly for each of the 23 units in 100 Participant Name areas, which is a lot of typing. There must be a coding solution... How can I use VBA to place the formula in the top row of each area (D1) and then fill down so that it changes F1 to G1 to H1 and B1 to C1 to D1 and so on for each of the 23 rows?

    Many thanks in anticipation...

  • 11 years ago

    This code will fill in D1 to D4 with the correct formula. I'll leave you to continue this for the rest of the sheet...

    Sub TransposeFormulas()
    Dim i As Integer
    Dim intRowOffsetP As Integer
    Dim intColOffsetP As Integer
    Dim intRowOffsetA As Integer
    Dim intColOffsetA As Integer
    intRowOffsetP = 0
    intColOffsetP = 2
    intRowOffsetA = 0
    intColOffsetA = -2
    For i = 1 To 4
        Cells(i, 4).Select
        ActiveCell.FormulaR1C1 = _
            "=IF('Participant List'!R[" & intRowOffsetP & "]C[" & intColOffsetP & "]="""","""",IF(Assessment!R[" & intRowOffsetA & "]C[" & intColOffsetA & "]=""C"",""Competent"",""Not Yet Competent""))"
        intRowOffsetP = intRowOffsetP - 1
        intColOffsetP = intColOffsetP + 1
        intRowOffsetA = intRowOffsetA - 1
        intColOffsetA = intColOffsetA + 1
    Next i
    End Sub
  • 11 years ago

    Thanks again. Saved a lot of hassle with find and replace

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“PHP is a minor evil perpetrated and created by incompetent amateurs, whereas Perl is a great and insidious evil perpetrated by skilled but perverted professionals.” - Jon Ribbens