所以我试图像以前一样将阵列粘贴回数千次,并且它粘贴了一些数据,但不是全部:
这是代码的一部分(我知道这行不通,但是项目太大了):
Sub Main()
AhorroMemoria True
Dim Temporizador As New Timer
Temporizador.StartCounter
With ThisWorkbook.Sheets("Main")
FechaI = Left(.Range("A2"),10)
FechaF = Right(.Range("A2"),10)
Reductor = .Range("B2")
End With
Dim RutaCompleta As String
RutaCompleta = CargaRutaCompleta(Ruta,FechaI)
Dim wb As Workbook
Set wb = Workbooks.Add
With wb.Sheets(1)
[A1:I1] = Array("Escenario","TERRITORIO","POOL","Agente","Rotativo","Sem Inicio Rotativo","Objetivo","Real","Objetivo Con reductor")
End With
Dim MiPool As New Pool
MiPool.CargaObjetivos RutaCompleta,wb
Dim arr As Variant
Cargaacc arr
Dim arrReales As Variant
arrReales = MiPool.EncuentraRotaciones(arr)
With wb.Sheets(1)
Dim LastRow As Long
LastRow = .Cells(.Rows.Count,1).End(xlUp).Row + 1
.Cells(LastRow,1).Resize(UBound(arrReales),UBound(arrReales,2)).Value = arrReales
Dim arrFinal As Variant
arrFinal = .UsedRange.Value
arr = .UsedRange.Value
Dim i As Long
For i = 2 To UBound(arrFinal)
If arrFinal(i,4) Like "Objetivo*" Then
arrFinal(i,9) = MiPool.CargaTurnosObjetivos(CStr(arrFinal(i,1)),CStr(arrFinal(i,3)),2)),5)),6)))
Else
Exit For
End If
Next i
.Range("A1").Resize(UBound(arrFinal),UBound(arrFinal,2)).Value = arrFinal
End With
Erase arr
Erase arrReales
Erase arrFinal
Set MiPool = Nothing
AhorroMemoria False
MsgBox "Comprobaciones realizadas en " & Format(Temporizador.TimeElapsed / 60000,"0.00") & " minutos."
End Sub
基本上,我正在尝试将日程表退回至最后一列。
一切正常,但数组不会将所有数据粘贴回范围。
在代码包含数组之前,
代码运行完另一个数组(原始副本)后:
如您所见,两者都具有相同的数据,仅出于调试目的更改了第9列。
这是代码运行之前的数据:
问题是,当我粘贴arrFinal
时,这就是我得到的:
我不明白为什么VBA不会将整个数组粘贴回工作表。我已经调试过.UsedRange.Address
并适合之前的版本,如果我粘贴回原始数组,VBA会将其整个粘贴...
有人以前遇到过类似的事情吗?
编辑:这段代码将按预期工作,复制整个数据。
Dim j As Long
For i = 1 To UBound(arrFinal)
For j = 1 To UBound(arrFinal,2)
Cells(i,j) = arrFinal(i,j)
Next j
Next i