PDA

View Full Version : [SOLVED] Why does my Array only fill half way?



plotpo
03-01-2020, 07:29 AM
Hey folks!

I can't work out how to use this 2D dynamic array. It builds exactly like it should, then only fills 5 values each and leaves the rest empty.
Maybe you guys can find the probably obvious mistake? :help

Behold my FrankenCode:



Sub Lieferschein3()

Worksheets("UBDateneingabe").Activate

Dim i As Integer, j As Integer
Dim Daten()
Dim size As Integer

size = Worksheets("UBDateneingabe").Range("A2", Worksheets("UBDateneingabe").Range("A2").End(xlDown)).Rows.Count

ReDim Daten(1 To 5, 1 To size)

For i = LBound(Daten, 1) To UBound(Daten, 1)
For j = LBound(Daten, 1) To UBound(Daten, 1)
Daten(i, j) = Cells(j + 1, i).Value
Next j
Next i

'Worksheets("Lieferschein").Range("A5:D6") = Daten

End Sub

Thanks in advance!

snb
03-01-2020, 08:23 AM
Vezichte au Activate und Select in VBA.

Ohne Array


Sub M_snb()
sheets("Lieferschein").Range("A5:D6").value = sheets("UBDateneingabe").range("A2:D3")
End Sub

Mit (überflüssige) Array

Sub M_snb()
sn=sheets("UBDateneingabe").range("A2:D3")
sheets("Lieferschein").Range("A5:D6") = sn
End Sub

plotpo
03-01-2020, 09:37 AM
Thanks, but that's not going to work since the source sheet is dynamic and I won't be able to change the code every time it is updated.
That's why I tried it via Array in the first place...


Vezichte au Activate und Select in VBA.

Ohne Array


Sub M_snb()
sheets("Lieferschein").Range("A5:D6").value = sheets("UBDateneingabe").range("A2:D3")
End Sub

Mit (überflüssige) Array

Sub M_snb()
sn=sheets("UBDateneingabe").range("A2:D3")
sheets("Lieferschein").Range("A5:D6") = sn
End Sub

SamT
03-01-2020, 10:57 AM
only fills 5 values
For j = LBound(Daten, 1) To UBound(Daten, 1) : means For J = 1 to 5 (Boundaries of Daten, 1). Should be boundaries of (Daten, 2)

Another alternate code
Sub SamT()
Dim Startcel As Range, EndCel As Range
Dim Daten

With Worksheets("UBDateneingabe")
Set StartCel = .Range("A2")
Set EndCel = .Cells(Rows.Count. "A").End(xlUp).Offset(, 5)
End With

Daten = Range(Startcel, Endcel).Value2

Worksheets("Lieferschein").Range("A5").Resize(Ubound(Daten, 1), Ubound(Daten, 2)) = Daten
End Sub

plotpo
03-01-2020, 11:04 AM
Of course, this was it! Thank you! :thumb


For j = LBound(Daten, 1) To UBound(Daten, 1) : means For J = 1 to 5 (Boundaries of Daten, 1). Should be boundaries of (Daten, 2)

Another alternate code
Sub SamT()
Dim Startcel As Range, EndCel As Range
Dim Daten

With Worksheets("UBDateneingabe")
Set StartCel = .Range("A2")
Set EndCel = .Cells(Rows.Count. "A").End(xlUp).Offset(, 5)
End With

Daten = Range(Startcel, Endcel).Value2

Worksheets("Lieferschein").Range("A5").Resize(Ubound(Daten, 1), Ubound(Daten, 2)) = Daten
End Sub

snb
03-01-2020, 02:45 PM
Oder:


Sub M_snb()
with sheets("UBDateneingabe").range("A2").currentregion.offset(1)
sheets("Lieferschein").Range("A5").resize(.rows.count,.columns.count) =.value
end with
End Sub