vlookup iserror with autofill to dynamic range - changing array/col index

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

first off I apologise as I am struggling to explain what I want to do:

I am trying to do vlookups (excel 2010) with the result of "unknown" if there is an error on a column of varying length, autofilling to the last row of adjacent column.

The current code I have works fine, however I want to include this in a loop where the vlookup values/array/column index number change depending on which worksheet the value has been taken from. I have tried to use application.vlookup and application.worksheetfunction.vlookup in the past but autofill only fills the first value not its formula.

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!

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!