Applying formula to a variable range

Question

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.

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.

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

Some hints: a) Fully qualify your range references to refer for sure to the wanted sheet. b) Assign values or formulae directly to cell ranges to [avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=SearchResults&s=1|221.1161). c) tl;dr Try to edit your post restricting yourself to only *some* significant data with a structured description of your goals. — T.M., Mar 28 '21 at 14:30
Hey T.M., thanks for the reply! I've edited the post, hopefully it is now better. I've looked through the link you sent but I still do not know how to do what I want, However, it is certainly an interesting read and I will try to improve the rest of my code based on it. Thanks! — spiritandtime, Mar 28 '21 at 15:04
If I understand you correctly you want this formula `"=FILTER(R[-29]C:R[-4]C[3],ISNUMBER(SEARCH(R[-1]C,R[-30]C:R[-30]C[3])))"` to be adjustable to the number of student (ms yin, ms jenny oh). So when more student writes (i.e. more data for columns K, L, M) the formula should expand to catch those students? Sounds like a fun challenge :) — Wizhi, Mar 28 '21 at 15:07
Hey Wizhi, yeap that's the rough gist. (The students are row 2 though- Potato Cake's Answer, etc and row 1 are the teachers) But yes essentially I want the forumla to extend to catch those students=) — spiritandtime, Mar 28 '21 at 15:11

Wizhi · Accepted Answer · 2021-03-28T16:22:47.527

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

Hey Wizhi, thanks a lot! Really appreciated that you added the comments so that a beginner like me can follow through. I guess that's one thing I should be working on too; I was too focused on getting things working and I haven't placed any comments for my codes. Thx and have a good day! — spiritandtime, Mar 29 '21 at 03:26
Thank you!! You're welcome :)!. Comments help both the programmer and the reader, especially after a while when you need to modify ^^. Just let me know if you need some more details or something doesn't work :). Happy coding :)! — Wizhi, Mar 29 '21 at 08:32

Applying formula to a variable range

1 Answers1