How to reference a cell within a dynamic range

Below is my current code which is not working. The variable "SH" is not being read and the result is no date is being picked up from the data. What I am trying to do with this data is reference a file for each row of the data, a file that is being referenced from the date in column K for each row. With the referenced file for each row, execute a VLOOKUP. If the file doesn't exist for that row, enter a 0 and move on to the next row -- each loop should only bring in one data point, the results of the VLOOKUP for that row.

Please Login or Register  to view this content.

worksheetfuntion is redundant, fix:
DTS = Range("K" & Rows.Count).End(xlUp).Row

Suppose you use 100 rows in column K then 100 will be loaded into the variable DTS, then the line:

Set DT = Sheets("Dividends").Range("K2" & DTS)

will append 100 to K2 resulting in range("K2100") note that this is a single cell

fix:
Set DT = Sheets("Dividends").Range("K2:K" & DTS)

To access the cell to the right of the current SH cell, use:
SH.Offset(0,1).Formula...
Where "0" means the same row & 1 means 1 cell to the right.

try:
SH.offset(0, 1).Formula= "=IFERROR(VLOOKUP(" & SH.Offset(0, -10).Value & ",'" & Folder1 & "[" & Eagle & "]" & SHTNM1 & "'!$A$2:$E$1000,5,0),0)"