VBA宏中使用单元格路径实现跨文件VLOOKUP的问题求助

Hey there, let's get that VLOOKUP issue sorted out in your macro. The core problem is that you're using a hardcoded [File2] placeholder instead of dynamically injecting the actual file path stored in your File2 variable, plus your VLOOKUP parameter order is reversed. Let's break down the fixes step by step.

Key Issues to Address: #

• You need to properly insert the File2 path (pulled from your Tool sheet's B3 cell) into the VLOOKUP formula, wrapped in single quotes (critical for handling paths with spaces).
• VLOOKUP's correct syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])—your current code has the parameters out of order.
• Ditching Select/Selection for direct Range operations makes your code faster, cleaner, and less prone to runtime errors.

Corrected vlookup Subroutine #

First, make sure File2 (and wb1) are accessible to the vlookup sub by declaring them as module-level variables. Put this at the very top of your module, above all subroutines:

' Module-level variables available to all subs in this module
Dim wb1 As Excel.Workbook
Dim File As String
Dim File2 As String

Then update the vlookup sub like this:

Sub vlookup()
    Dim externalLookupRange As String
    ' Build the external workbook reference (single quotes handle spaces in file paths)
    externalLookupRange = "'" & File2 & "'!Sheet1!$A$2:$H$1000"
    
    ' Work directly with the open File1 workbook (wb1) to avoid confusion
    With wb1.Sheets("YourTargetSheetName") ' Replace with your actual sheet name in File1
        ' Assume we're looking up values from column G (adjust this to your actual lookup column)
        .Range("H2").Formula = "=VLOOKUP(G2, " & externalLookupRange & ", 8, FALSE)"
        ' Fill the formula down to the last row you need
        .Range("H2:H538").FillDown
    End With
End Sub

Quick Checks & Tips: #

• Verify the lookup range: Ensure the value you're searching for is in the first column of externalLookupRange (here, column A of Sheet1 in File2), and the column you want to return is the 8th column (adjust this number to match your actual data).
• Debug the file path: If you're unsure if File2 holds the correct path, add MsgBox File2 right after assigning it in Past_dues_button12345 to confirm it's a full, valid path (like C:\Documents\File2.xlsx).
• Avoid Select wherever possible: Your add_columns_with_comments sub uses a lot of Select calls—you can rewrite those to target ranges directly too, for example:

  Sub add_columns_with_comments()
      Columns("F:F").Resize(,3).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      Range("Table1[[#Headers],[Column3]]").Value = "PN"
      Range("Table1[[#Headers],[Column2]]").Value = "MRPc"
      Range("Table1[[#Headers],[Column1]]").Value = "Comment"
  End Sub
  

Full Adjusted Module Snippet #

Here's how the top of your module will look with the module-level variables and corrected vlookup sub:

' Module-level variables accessible to all subs
Dim wb1 As Excel.Workbook
Dim File As String
Dim File2 As String

Sub Past_dues_button12345()
'Macro to create past due list daily
    File = Sheets("Tool").Range("B2")
    File2 = Sheets("Tool").Range("B3")
    Set wb1 = Workbooks.Open(File)
    remove_repair
    add_columns_with_comments
    add_data_new_column
    vlookup
    pastevalues
    Sharewb
End Sub

' ... keep your other subs (remove_repair, add_data_new_column, etc.) as is ...

Sub vlookup()
    Dim externalLookupRange As String
    externalLookupRange = "'" & File2 & "'!Sheet1!$A$2:$H$1000"
    
    With wb1.Sheets("YourTargetSheetName") ' Update to your actual sheet name in File1
        .Range("H2").Formula = "=VLOOKUP(G2, " & externalLookupRange & ", 8, FALSE)"
        .Range("H2:H538").FillDown
    End With
End Sub

This should get your VLOOKUP pulling data from File2 correctly. Let me know if you need to tweak the range or lookup column to match your exact setup!

内容的提问来源于stack exchange，提问作者Willem Martens