Applying formula to a variable range

user_15475814 0
Viewed 83

I would like to apply a formula to a given range.
However, the number of columns are not fixed and will vary.

Screenshots to visualise what I'm doing.

Screenshot 1: I would like for the code to auto select from column C onwards, and apply the formula in the next image. The number of columns will vary as more students attempt the quiz. enter image description here

Screenshot 2: This is the formula I wish to apply to the selected range. After that, I would be able to loop through the list of teachers from B31 and below one by one, copy the range of answers for each teacher's students and paste them onto Sheets 3-6 which contain the first set of results I mentioned earlier.
enter image description here

Sub obtainsecond()
    Sheets("Question_answers").Select
    Range("C31").Select
    ActiveCell.Formula2R1C1 = _
      "=FILTER(R[-29]C:R[-4]C[3],ISNUMBER(SEARCH(R[-1]C,R[-30]C:R[-30]C[3])))"
End Sub
vba excel
1 Answers

One approach to solve the problem.

This approach assumes that the last column in row 1 is the last column with a student answer.

Logic: I check the last column and get the cell reference (i.e. $H1). Then i extract only the column letter. I take the column letter and put it in the formula you want to extend.

Code:

Option Explicit

Sub obtainsecond()

Dim QA_ws As Worksheet 'Declare the worksheet as a variable
Set QA_ws = ActiveWorkbook.Worksheets("Question_answers") 'Decide which worksheet to declare

Dim lCol As Long
Dim LastColumnLetter As String
Dim lColRange As Range
    
    QA_ws.Activate 'Go to the worksheet
    lCol = QA_ws.Cells(1, Columns.Count).End(xlToLeft).Column 'Find the last column in the worksheet by checking in row 1
    
    Set lColRange = QA_ws.Cells(1, lCol) 'Set last column to get cell reference, i.e. $H1
    'MsgBox lColRange.Address(RowAbsolute:=False) ' $H1
    
    'https://www.exceltip.com/tips/how-to-convert-excel-column-number-to-letter.html (Formula to extract letter: =SUBSTITUTE(ADDRESS(1,B2,4),1,””))
    LastColumnLetter = WorksheetFunction.Substitute(lColRange.Address(RowAbsolute:=False), "1", "") 'Get column letter
    LastColumnLetter = Replace(LastColumnLetter, "$", "") 'Remove prefix
    
    QA_ws.Range("C31").Formula2 = "=FILTER(C2:" & LastColumnLetter & "27,ISNUMBER(SEARCH(C30,C1:" & LastColumnLetter & "1)))" 'Use relative formula to print in cell (original formula: =FILTER(C2:F27,ISNUMBER(SEARCH(C30,C1:F1))))

End Sub
user_45525290
Related