Consulting

Results 1 to 3 of 3

Thread: VLookup with dynamic Filename

  1. #1
    VBAX Regular
    Joined
    Dec 2007
    Posts
    15
    Location

    VLookup with dynamic Filename

    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

    [VBA]
    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
    [/VBA]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Dec 2007
    Posts
    15
    Location
    Quote Originally Posted by xld
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •