Vlookup in VBA to another Workbook

[BayEnder111]

Hi All,

A bit of a strange one here and I may be approaching it completely in the wrong way but....

I'm trying to perform a Vlookup in VBA from my main workbook against another workbook, however this workbooks name changes each week. Therefore I've created some code to look for the word "Open" in a folder path and open the workbook with this in.

Due to this workbook never having a set name I've defined the workbook, sheet and range so I can reference it in the Vlookup.

In my main workbook I've created a loop to go through a range of cells (D2:D2500) and if they aren't blank perform the vlookup. The Cell Offset is used to determine which cell to output the result to.

I'm getting a type mismatch error when I perform the below:

strPath = "C:\Users\\Desktop\Automator" strFile = Dir(strPath & "\" & "*Open*" & ".xls")   


If Len(strFile) > 0 Then
  Workbooks.Open strPath & "\" & strFile   
  Sheets(2).Select 
  myFileName = ActiveWorkbook.Name
  mySheetName = ActiveSheet.Name
  myRangeName = Range("E3:AP2500")   
  
ThisWorkbook.Activate
Set rng = Range("D2:D2500") 
    For Each Cell In rng
        If Cell.Value <> "" Then 
           Cell.Offset(0, 15).Value = "=VLOOKUP(C2,[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",2,False)"
    End If
    Next
Else
  MsgBox "Error: Open Problem Lifecycle Not Found": Exit Sub 
End If

Any help would be greatly appreciated.

Thanks.

 

[Domenic]

Try...

myRangeName = Range("E3:AP2500")[COLOR=#ff0000].Address[/COLOR]

Hope this helps!

 

[BayEnder111]

That's fixed the range error however I'm now getting an error on the highlighted line below:


"Run-Time error '1004':
Application-defined or object-define error

strPath = "C:\Users\\Desktop\Automator" strFile = Dir(strPath & "\" & "*Open*" & ".xls")   




If Len(strFile) > 0 Then
  Workbooks.Open strPath & "\" & strFile   
  Sheets(2).Select 
  myFileName = ActiveWorkbook.Name
  mySheetName = ActiveSheet.Name
  myRangeName = Range("E3:AP2500").Address   
  
ThisWorkbook.Activate
Set rng = Range("D2:D2500") 
    For Each Cell In rng
        If Cell.Value <> "" Then 
           Cell.Offset(0, 15).Value = "=VLOOKUP(C2,[" & myFileName & "]" & mySheetName & "!" & myRangeName & ",2,False)"
    End If
    Next
Else
  MsgBox "Error: Open Problem Lifecycle Not Found": Exit Sub 
End If

After some debugging I think the issue is that when I'm setting "myFileName" it's referencing my main workbook rather than the one I want to lookup from (With the changing file name).


Is there a way to activate the workbook that has the changing file name?


I may be completely down the wrong path but that's my guess!


Any help greatly appreciated.


Thanks.

 

[RoryA]

You appear to be missing some apostrophes in the formula:

"=VLOOKUP(C2,'[" & myFileName & "]" & mySheetName & "'!" & myRangeName & ",2,False)"

 

[davesexcel]

is

  ThisWorkbook.Activate

Supposed to be from the original workbook?
I can see the code thinking ThisWorkbook being the active workbook.
Might be best to set the variables for your original workbook, before you open the other workbook