Getting an error when doing a VLOOKUP

[2quik4u2]

Hi I am running the following code, trying to check and see if values in a cell have changed everytime the sub runs (through a button on the worksheet. Essentially I am looking up data in another closed excel file called "TODAY" comparing it to data in the new workbook and if there is any difference updating the values. but I am getting an "Runtime error: 9 subscript out of range" error. Can anyone help me?

Sub UpdateWorksheet()
Range("C3").Copy
Range("D3").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Dim tp As Integer
Dim risk As String
Dim rating As String
Dim drequested As Range
Dim row As Integer
Dim ddate As String
Dim rng As Range

For row = 5 To 119
tp = Application.WorksheetFunction.VLookup(Cells(row, 2).Value, Workbooks("TODAY.xls").Worksheets(1).Range("$D$2:$Y$307"), 10, False)

risk = Application.WorksheetFunction.VLookup(Cells(row, 2).Value, Workbooks("TODAY.xls").Worksheets(1).Range("$D$2:$Y$307"), 21, False)
rating = Application.WorksheetFunction.VLookup(Cells(row, 2).Value, Workbooks("TODAY.xls").Worksheets(1).Range("$D$2:$Y$307"), 20, False)


If Cells(row, 4) <> tp Or Cells(row, 5) <> rating Or Cells(row, 8) <> risk Then

Cells(row, 8) = Application.WorksheetFunction.VLookup(Cells(row, 2).Value, Workbooks("TODAY.xls").Worksheets(1).Range("$D$2:$Y$307"), 1, False)
Cells(row, 5) = Application.WorksheetFunction.VLookup(Cells(row, 2).Value, Workbooks("TODAY.xls").Worksheets(1).Range("$D$2:$Y$307"), 20, False)
Cells(row, 4) = Application.WorksheetFunction.VLookup(Cells(row, 2).Value, Workbooks("TODAY.xls").Worksheets(1).Range("$D$2:$Y$307"), 10, False)
Cells(row, 6) = ddate

End If

Next row

End Sub

 

[bensonsearch]

this error usually occurs when the data is to large for the variable to handle.

maybe start by changing the integers to long