是否可以在 vba 中为数组分配一列

Question

我正在尝试根据列位置映射两个 excel 电子表格 我正在创建两个单独的数组，想知道是否可以将列分配给数组示例

dim ex 1 to 2 as string
ex(1) = column("A")
ex(2) = column("B")

等等。

我正在实现这两个数组

Private Sub CommandButton1_Click()
Dim source As Workbook
Dim sht1 As Worksheet
Dim dest As Workbook
Dim sht2 As Worksheet
Dim tmp As String
Dim startCell As Range
Dim lastRow As Long
Dim lastColumn As Long
Dim i As Integer
Dim j As Integer
Dim mapDest As String
Dim mapSrc As String
Dim data As String
Dim iRows As Integer
Dim pos As Integer
setFile = Application.GetOpenFilename   'used to open the browser window
tmp = setFile                           'store the selected file in variable tmp
Application.ScreenUpdating = False      'preventing long runtimes
If Dir(tmp) <> "" Then
Set dest = ThisWorkbook     'workbook b1 is declared as the current worksheet opened
Set source = Workbooks.Open(tmp)      'the file the user selected is opened in excel
Dim lasSheets(1 To 8) As String        'array to list the sheet names
lasSheets(1) = "L1 OVERVIEW"
lasSheets(2) = "LAS EFFL RELEASE PARAMS"
'lasSheets(3) = "L1 EAL PARAMS rev4"    'now use a rev 6 so 4 is no longer used
lasSheets(3) = "L1 EAL PARAMS"
lasSheets(4) = "L1 RAD STATUS"
lasSheets(5) = "L1 PLANT STATUS"
lasSheets(6) = "L1 CDAM"
lasSheets(7) = "L1 ERDS"
lasSheets(8) = "LAS STATE UPDATES"
Dim dataPull(1 To 8) As String          'array to map the worksheet names located in the data pull sheet
dataPull(1) = "Overview Paste"
dataPull(2) = "Eff Release Para Paste"
'dataPull(3) = "EAL Rev4 Paste" 'blank sheet now use rev 6 no longer needed
dataPull(3) = "EAL Para Paste"
dataPull(4) = "Radiological Stat Paste"
dataPull(5) = "Plant Status Paste"
dataPull(6) = "CDAM Paste"
dataPull(7) = "ERDS Paste"  ' blank sheet
dataPull(8) = "State Updates Paste"   'blank sheet
Dim lasSheetsCols(1 To 16) As String
LasSheetCols(1) = .Columns("A")                 ***is ir possible to map columns?
LasSheetCols (2) =
LasSheetCols (3)
LasSheetCols (4)
LasSheetCols (5)
LasSheetCols (6)
LasSheetCols (7)
LasSheetCols (8)
LasSheetCols (9)
LasSheetCols (10)
LasSheetCols (11)
LasSheetCols (12)
LasSheetCols (13)
LasSheetCols (14)
LasSheetCols (15)

Dim dataPullCols(1 To 9) As String

For i = 1 To 8
  mapSrc = dataPull(i)
  mapDest = lasSheets(i)
 Set sht1 = source.Sheets(mapSrc)      'set sht1 and sht2 to the source and destination worksheets
 Set sht2 = dest.Sheets(mapDest)
 lResult = Right(lastCol, 10)
 Set startCell = sht1.Range("B2")
 Set checkcell = sht1.Cells
'find last row and last column
 lastRow = sht1.Cells(sht1.Rows.Count, startCell.Column).End(xlUp).Row
 lastColumn = sht1.Cells(startCell.Row, sht1.Columns.Count).End(xlToLeft).Column
 lastCol = sht1.Cells(sht1.Columns.Count).End(xlToLeft).Column

sht1.Range(startCell, sht1.Cells(lastRow, lastColumn).Address).Copy destination:=sht2.Range("D5")       'copy and paste the data from sht1 into sht2

Application.CutCopyMode = False
pos = 5
For j = 2 To lastRow         ' for loop to loop through columns A
pos = pos + 1
With sht1
     data = Right(sht1.Cells(j, 1), 11)  'retrieve the values in the cells
      sht2.Cells(pos, 1) = data
      data = ""
      End With
   Next j

Next i                              ' loop through the indexes
source.Close True
Else                                'used to prevent a error message from popping up when the user choose to cancel selecting a file
End If
End Sub

按下按钮时，数据从一个工作表复制，并根据映射在一起的数据粘贴到另一个工作表中

Answer 1

您可以将整个范围分配给变量：

Dim MyRangeValues() As Variant
MyRangeValues = Range("A:O").Value

现在你的变量MyRangeValues包含 A 到 O 列，它可以像

Debug.Print MyRangeValues(row, column) 

按行/列打印到特定单元格的值。所以例如

MyRangeValues(2, 3) 

将返回单元格C2的值。