Apologies, here is the same code with code tags. I wrote this after a day of staring at various code annoyances so I accidentally missed rule 3


current code

Set myrange = Sheets("temp_for_calcs").Columns("c:c") 'setting range for countif of variable length column
Sheets("temp_for_calcs").Select
Range("d2").Formula = "=countif(b:b,c2)" 
countnonblank = Application.WorksheetFunction.CountA(myrange) 

Range("e2").Formula = "=IFERROR(VLOOKUP(c2,flag_matrix!A:be,57,FALSE),""unknown"")" ' <<<<<<<< here i want to edit so the a:be / 57 are replaced by variables v_array and v_index
Range("f2").Formula = "=IF(e2<>""unknown"",d2*e2,""unknown"")"
Range("i2").Formula = "=sum(d:d)" 'total flag count
Range("g2").Formula = "=IF(e2<>""unknown"",d2/$i$2 *100,""unknown"")" '% of total score

If countnonblank > 2 Then 'accounts for 1 value and column header, stops autofill error if no rows to fill
' fills to the end of data in row b
'''CHANGE BELOW''' table array and column index number'
Range("d2").AutoFill Destination:=Range("d2:d" & countnonblank)
Range("e2").AutoFill Destination:=Range("e2:e" & countnonblank)
Range("f2").AutoFill Destination:=Range("f2:f" & countnonblank)

Range("g2").AutoFill Destination:=Range("g2:g" & countnonblank)

End If


any idea how I can use vlookup (including an 'unknown' on error) that I can assign preset values for the table array and index number that can be autofilled down a column of varying length?



thank you for trying to understand my question!