Excel VBA transposition problem

• • Reply

  11 years ago

  by KDHTSVAU

  Kevin Hubbard

  Townsville, Australia

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

  Report abuse
• • Reply

  11 years ago

  by Uncle

  Uncle

  England, United Kingdom

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

  Report abuse
• • Reply

  11 years ago

  by KDHTSVAU

  Kevin Hubbard

  Townsville, Australia

  Joined 11 years ago

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

  Report abuse