VLookup with dynamic Filename

[Banixxx]

I have an excel file that is dumped from a stats server and the file name is the same except for it ends with the day of the dump. I want a macro to write a VLOOKUP based on a date in a cell in order to look in the right file. Example code below. Here is my current code but there must be an easier way


Sub calcStats()

Dim aCol As String
Dim xCol As String
Dim sCol As String
Dim pCol As String
Dim fileLocation As String
Dim fileDate As String
Dim currDate As String
Dim fileExt As String
Dim FileName As String
Dim agentRow As String
Dim agentLevel As String
Dim L1Range As String
Dim L2Range As String
Dim theRange As String
Dim ahtString As String
Dim xferString As String
Dim surveyString As String
Dim poseString As String
Dim x As Integer
Dim y As Integer

x = 3
y = 12
aCol = "6"
xCol = "9"
sCol = "11"
pCol = "12"

currDate = Format(Date, "YYYY-MM-DD")
fileLocation = "'\\Pifr\pifrweb\QCM\Supervisor_Scorecard\Daily\[2wire_Daily_Scorecard_"
fileExt = ".htm]"
L1Range = "$B$10:Q$1000"
L2Range = "$B$10:$R$1000"

Application.ScreenUpdating = False

Do Until Cells(x, 1).Value = ""

agentRow = x
agentLevel = Cells(x, 4).Value
FileName = "Level " + agentLevel + " Scorecard'!"

If (agentLevel = "1") Then
theRange = L1Range
Else
theRange = L2Range
End If

Do Until Cells(x, y).Value = ""

' Calculate Stats for DAily 1-5 Sheet
fileDate = Cells(x, y).Value
fileDate = Format(fileDate, "YYYY-MM-DD")

If (fileDate < currDate) Then
ahtString = "VLOOKUP(A" + agentRow + "," + fileLocation + fileDate + fileExt + FileName + theRange + "," + aCol + ",FALSE)"
xferString = "VLOOKUP(A" + agentRow + "," + fileLocation + fileDate + fileExt + FileName + theRange + "," + xCol + ",FALSE)"
surveyString = "VLOOKUP(A" + agentRow + "," + fileLocation + fileDate + fileExt + FileName + theRange + "," + sCol + ",FALSE)"
poseString = "VLOOKUP(A" + agentRow + "," + fileLocation + fileDate + fileExt + FileName + theRange + "," + pCol + ",FALSE)"

ahtString = "=IF(ISERROR(" + ahtString + "),," + ahtString + ")"
xferString = "=IF(ISERROR(" + xferString + "),," + xferString + ")"
surveyString = "=IF(ISERROR(" + surveyString + "),," + surveyString + ")"
poseString = "=IF(ISERROR(" + poseString + "),," + poseString + ")"

Cells(x, y + 1).Value = ahtString
Cells(x, y + 2).Value = xferString
Cells(x, y + 3).Value = surveyString
Cells(x, y + 4).Value = poseString

y = y + 8
Else
'Do Nothing and Move to Next File
y = y + 8
End If

Loop
y = 12
x = x + 1
Loop


Application.ScreenUpdating = True

End Sub

[Bob Phillips]

What is wrong with your approach?

The formula is dependent upon values in cells, so these need to be tested. You coud put the test in the formula, but I wouldn't recommend it.

[Banixxx]

What is wrong with your approach?

The formula is dependent upon values in cells, so these need to be tested. You coud put the test in the formula, but I wouldn't recommend it.


xld,

I was wondering if their was an easier way to do this to make it more dynamic like a function similary to the VLOOKUP of a Nth value. Is there a way i can make this SUB into a function where when a user types something like DVLOOKUP in the formual bar in excel they change change the Directory fileName, File date, file ext and have it do the vLookup and return the value?