Use VLOOKUP in a dynamic range using VBA

Use VLOOKUP in a dynamic range using VBA

Having a hard time figuring out the syntax of a lookup and vlookup in vba
excel.
Here's my code.
Public Sub State()
Dim i As Integer
Dim count As Integer
count = Sheet2.Range("H1")
For i = 0 To count
Sheet2.Range(Sheet).Formula = _
"=VLOOKUP(" & Sheet2.Name & "!R8C" & 4 + i & "," & Sheet13.Name &
"!A:C,2,TRUE)"
Next i
End Sub
What I'm trying to do is Get a vlook up based on the cell above me as what
i'm trying to look up. Go to a different sheet and find the values, return
the lookup from column 2. Then I need it to iterate based on the count
value horizontally. So; for example, I get value from D8, I return it do
D9, and E8, to E9 for as long as count variable goes.
I'm super confused on what I'm even asking my code to do because of all
the " " and & &.
There has to be a better way to format this.
EDIT:
Here's what I have based on Alex's code.
Public Sub State()
Dim refRng As Range, ref As Range, dataRng As Range
Dim dynamicRange As Integer
dynamicRange = Worksheets(2).Range("H4")
' =VLOOKUP(Output!D8,CBSA_Master!A:C,2,TRUE) This is the vlookup I'm
trying to match
Set refRng = Worksheets(2).Range("D8:F" & dynamicRange + 2)
'//horizontal range of look up values
Set dataRng = Worksheets(13).Range("A:C") '//data block you want to
look up value in
For Each ref In refRng
ref.Offset(1, 0) = WorksheetFunction.VLookup(ref, dataRng, 2, 0)
Next ref
End Sub
I'm getting a new Runtime error 9. subscript out of range error. It's not
highlighting anything that is wrong though.