Question vlookup vba

Try this code

Sub Test()
    Dim i As Integer
    
    With Worksheets("Input")
        For i = 6 To 8
            .Cells(i, 8).Value = Application.WorksheetFunction.VLookup(.Cells(i, 7), .Range("G1:H4"), 2, 0)
        Next i
    End With
End Sub

Better:

Sub Test()
Dim r As Range, i&    'Excel works with longs, your integer would be permanently converted to long; more - Cells property needs longs as parameters!
With <write the CODENAME of the sheet "Input" here>    'Why are sooo loved ugly (slow to use for VBA) sheet names in this forum instead of simple quick-to-use codenames?
    Set r = .Range("G1:H4")    'Why to permanently repeat the construction of the range in the for-cycle body?
    For i = 6 To 8
        .Cells(i, 8) = WorksheetFunction.VLookup(.Cells(i, 7), r, 2, 0)    'worksheetfunction object can be called directly
    Next i
End With
End Sub

More: DON'T use explicit addressing, use NAMED ranges!!! No
Set r = .Range("G1:H4")
that must be changed whenever the sheet is reorganized (by deleting or inserting rows or columns)! Give some workbook-scoped name to the range, say, 'lookTbl', and write

Sub Test()
Dim r As Range, i&
Set r = [lookTbl]
With <write the CODENAME of the sheet "Input" here>
    For i = 6 To 8
        .Cells(i, 8) = WorksheetFunction.VLookup(.Cells(i, 7), r, 2, 0)
    Next i
End With
End Sub

Think yourself how to change i = 6 To 8 and Cells(i, 8), Cells(i, 7) to avoid making changes in code when the sheet is reorganized.

I would agree that named ranges are a great method for setting ranges of cells a macro can use without needing to edit the VBA should the range need to change.

I would not agree that codenames should be used over sheet names, that is a preference, one you obviously feel strongly about, but more issues will require code editing more often with codename usage over sheetname usage.