VBA引用不同工作表中的表

2 回答

• 1

  您可以在VBA中将工作簿，工作表和范围设置为对象，并按名称引用它们 .

  例：

  Sub test()
  
  Dim filename As String
  Dim fullRangeString As String
  
  Dim returnValue As Variant
  Dim wb As Workbook
  Dim ws As Worksheet
  
  Dim rng As Range
  
      'get workbook path
      filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
  
  
      'set our workbook and open it
      Set wb = Application.Workbooks.Open(filename)
  
      'set our worksheet
      Set ws = wb.Worksheets("Sheet1")
  
      'set the range for vlookup
      Set rng = ws.Range("A3:I13")
  
  
      'Do what you need to here with the range (will get error (unable to get vlookup property of worksheet) if value doesn't exist
      returnValue = Application.WorksheetFunction.VLookup("test4", rng, 2, False)
  
  
      MsgBox returnValue
      'If you need a fully declared range string for use in a vlookup formula, then
      'you'll need something like this (this won't work if there is any spaces or special
      'charactors in the sheet name
  
  
  
      'fullRangeString = "[" & rng.Parent.Parent.Name & "]" _
                          & rng.Parent.Name & "!" & rng.Address
  
      'Sheet1.Cells(10, 10).Formula = "=VLOOKUP(A1," & fullRangeString & ",8,False)"
  
  
  
  
      'close workbook if you need to
      wb.Close False
  
  
  End Sub
  

  回复于 2024-05-05T20:52:56+08:00
• 2

  这应该工作：

  Sub vLook()
  
  Dim wb1 As Workbook
  Dim wb2 As Workbook
  Dim Range1, myValue
  Set wb1 = ThisWorkbook
  Set wb2 = Workbooks.Open("C:\Code\Book4.xlsx")
  
  Range1 = wb2.Sheets(1).Range("A1:C5")
  myValue = Application.WorksheetFunction.VLookup("Test", Range1, 2, False)
  
  End Sub
  

  回复于 2024-05-05T20:52:56+08:00