Why does file name appear twice in my VBA Vlookup Formula?

[MixedUpExcel]

Hi,

When I run this code.

My aim is to have a user be able to pull data based on their selected criteria from the inputbox from a closed file (which isn't going to be opened).

That closed file is updated (with a new file name) each week, hence the user having to select the file initially to obtain the file name.

The vlookup is resulting in part of the folder / file name being input twice into the formula.

Please can you advise where my mistake is?

Thanks in advance.

Simon

Sub data_file()

Dim MYVALUE As Variant
Dim FName As Variant
Dim Msg As String


    workbooknamemain = ActiveWorkbook.Name


    MYVALUE = InputBox("PLEASE SELECT JUST THE NUMBER FOR YOUR REQUIRED OPTION" & vbCrLf & _
            "1 - STATUS" & vbCrLf & _
            "2 - DESCRIPTION" & vbCrLf & _
            "3 - COST PRICE" & vbCrLf & _
            "4 - SELL PRICE" & vbCrLf & _
            "5 - RRP EX VAT" & vbCrLf & _
            "6 - SALES RANGE")


If MYVALUE = 1 Then
    mycol = 4
ElseIf MYVALUE = 2 Then
    mycol = 3
ElseIf MYVALUE = 3 Then
    mycol = 9
ElseIf MYVALUE = 4 Then
    mycol = 10
ElseIf MYVALUE = 5 Then
    mycol = 27
ElseIf MYVALUE = 6 Then
    mycol = 25


End If


    FName = Application.GetOpenFilename()
    
If FName <> False Then
 
Else
 
End If


    MsgBox FName
 
    Range("I2").Value = FName
  
Dim MyRange As Range


Set MyRange = Selection


    Selection.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[" & FName & "]'!C5:C32," & mycol & ",0)"


End Sub

 

[davesexcel]

Possibly, not having a sheet name in the formula could be an issue

 

[6StringJazzer]

Possibly, not having a sheet name in the formula could be an issue

I agree this is the problem. If you try to type a formula like that directly into a cell you will have the same problem. Excel is trying to fix it for you. Insert sheet name where shown:

    Selection.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[" & FName & "][COLOR="#FF0000"]SheetName[/COLOR]'!C5:C32," & mycol & ",0)"

 

[MixedUpExcel]

Hi,

I had the sheet name included first but it was duplicating that, so I removed it.

I've added it back in to see what would happen and after it's added the formula to the cell, it give me the following, which is incorrect:

=VLOOKUP(B12,'[C:\Users\Simon Ashton\Documents\[test file.xlsx]S1]test file.xlsx]S1'!$E:$AF,3,0)

The formula is slightly different (range etc) because I'm working on a test file from home, rather than the main files at work.

That was from inserting the following code into the cell:

Selection.FormulaR1C1 = _
    "=VLOOKUP(RC[-1],'[" & FName & "]S1'!C5:C32," & mycol & ",0)"

Have I misunderstood something?

Thanks.

 

[6StringJazzer]

OK, you have two problems.

First, the [brackets] go only around the file name. You are putting them around the entire path name.

Second, you are using A1 addressing to create an R1C1 formula. Instead of C5:C32 you have to use R5C3:R32C3.

Here is tested code that will work. You will have to insert your code to set mycol, and so forth.

   Dim FullPath As String
   Dim FolderPath As String
   Dim FArr As Variant
   Dim FileName As String
   Const mycol = 5
   
   
   FullPath = Application.GetOpenFilename()
   FArr = Split(FullPath, "\")
   FileName = FArr(UBound(FArr))
   FolderPath = Replace(FullPath, FileName, "")
   
  
   Selection.FormulaR1C1 = _
       "=VLOOKUP(RC[-1],'" & FolderPath & "[" & FileName & "]S1'!R5C3:R32C3," & mycol & ",0)"

 

[MixedUpExcel]

Thanks for your help today 6StringJazzer.

I've just had a quick play with your code and it's working like you said.

Now I have something that works, I can incorporate it into my main code.

Appreciate your time.

Thanks again.

 

[Fluff]

@6StringJazzer
The C5:C32 in the formula is fine as they are complete column references (a bad idea) relating to columns E:AF
@MixedUpExcel
I think that you will find a problem, if you have used the formula in post#5 as the lookup rang is only 1 column wide.

 

[6StringJazzer]

@6StringJazzer
The C5:C32 in the formula is fine as they are complete column references (a bad idea) relating to columns E:AF
@MixedUpExcel
I think that you will find a problem, if you have used the formula in post#5 as the lookup rang is only 1 column wide.

Absolutely right, I don't know what I was thinking.

(The file name part is correct.)

 

[MixedUpExcel]

@6StringJazzer
The C5:C32 in the formula is fine as they are complete column references (a bad idea) relating to columns E:AF
@MixedUpExcel
I think that you will find a problem, if you have used the formula in post#5 as the lookup rang is only 1 column wide.

Hi Fluff,

The report that I'm going to be pulling data from (but not opening) is about 400,000 Rows and grows each week.

I am going to change it to look at 500,000 rows (rather than reference the whole column) and then reassess in the future.

Regarding the 1 column wide part. I'd worked that bit out and changed it accordingly so that it worked for me.

I couldn't have got to that point though without 6StringJazzers help with the rest of that. So I'm still grateful for the help.

Thanks.