Banixxx
02-05-2008, 10:26 PM
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
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