chemicalfan's Profile User Rating: -----

Reputation: 4 Apprentice
Active Members
Active Posts:
94 (0.05 per day)
16-October 09
Profile Views:
Last Active:
User is offline May 19 2015 12:59 AM

Previous Fields

OS Preference:
Favorite Browser:
Favorite Processor:
Favorite Gaming Platform:
Your Car:
Dream Kudos:

Latest Visitors

  • PhotoHiram Icon
    26 Aug 2011 - 06:51
Icon   chemicalfan has not set their status

Posts I've Made

  1. In Topic: [VBA] Set VLOOKUP using second workbook

    Posted 15 May 2015

    Thanks for the link, I've got it to stop producing the error now, but for reasons I can't see, it mangles the link, causing Excel to prompt the user for the correct path & sheet for "update values". The code is now as follows:
    Dim strFileToOpen as String
    strFileToOpen = "False"
            strFileToOpen = Application.GetOpenFilename(Title:="Please choose a file to open", FileFilter:="Excel Files *.xls* (*.xls*),")
            If strFileToOpen = "False" Then
                Response = MsgBox("No file selected", vbRetryCancel + vbExclamation)
                If Response = vbCancel Then
                    Exit Sub
                End If
            End If
        Loop While strFileToOpen = "False"
        Workbooks.Open Filename:=strFileToOpen
    ' MsgBox strFileToOpen
    ActiveCell.Formula = "=VLOOKUP(S2,'" & strFileToOpen & "extract'!$AP:$AP,1,FALSE)"

    Not sure what's gone on with the formatting there...
    Anyway, I was expecting the output in that cells to be:


    =VLOOKUP(S2,'[5th May 2015 WiP extract.xlsx]extract'!$AP:$AP,1,FALSE)

    What I get is:


    =VLOOKUP(S2,'S:\XXXXXX\[5th May 2015 WiP extract.xlsxextract]extract'!$AP:$AP,1,FALSE)

    Ignoring the network path (can't see how that would be the issue necessarily, although it is unnecessary as the extract is an open workbook). It's a bit confusing with all the "extract" in there - both the last word in the path, and the sheet are called extract. I tried referring to a named range on the "5th May.." workbook, but that failed too.


    Might have discovered a clue - before going through the "update values" prompts that Excel throws, I can see the formula in the formula bar, and it's got the path to the workbook correct, but the name of the workbook is wrong (it has "extract" concatenated onto the end of the filename. Also, it has the sheet down as "5th May..." filename, without the xlsx extension). I've no idea where it's got all that, as the Msgbox display of the variable is correct.
  2. In Topic: [VBA] Set VLOOKUP using second workbook

    Posted 12 May 2015

    It does, it is a Windows network path. The lookup works if manually created in the cell, it doesn't work (or even attempt to get created) from the VBA macro
  3. In Topic: [VBA] Set VLOOKUP using second workbook

    Posted 11 May 2015

    I've set the ActiveCell using the Range.Select command immediately, not sure where the problem is? I do use Windows(2).Activate to switch between the workbooks (as the name can vary, I can't hardcode it), but the Range.Select still works, so I guess there's no problem?

    If by "step through", you mean F8, then yep, I use it all the time :)
  4. In Topic: [VBA] Set VLOOKUP using second workbook

    Posted 8 May 2015

    Fast reply not working for some reason...

    The error is the same as the OP - "Application-defined or object-defined error", there's no output to the cell as the macro breaks at this point. It even breaks using activecell.value rather than formula! I wonder if it's something to do with the expansion of the variable it doesn't like?
  5. In Topic: [VBA] Set VLOOKUP using second workbook

    Posted 8 May 2015

    Thanks for the reply! Unfortunately, it's still not playing. I've tweaked the code to read:
    strVLFilename = "[" & strFileToOpen & "]"
        MsgBox strVLFilename
        ActiveCell.FormulaR1C1 = "=VLOOKUP(S2,'" & strVLFilename & "extract'!$AP:$AP,1,FALSE)"

    strVLFilename is defined as a string, and the msgbox confirms the path is correct. The other workbook is also open (fails whether closed or open), and the network path is accessible.

    Another other ideas? :(

My Information

Member Title:
D.I.C Head
31 years old
August 31, 1983
Portsmouth, UK
Programming Languages:

Contact Information

Website URL:
Website URL  http://


chemicalfan hasn't added any friends yet.


Page 1 of 1
  1. Photo

    Hiram Icon

    26 Aug 2011 - 06:51
    Always good to see a fellow Arch user! :3
Page 1 of 1