Na, dann zB so, ,,,
06.10.2013 04:30:16
Luc:-?
…Benny:
Rem Prozedur verwendet stets das aktuelle Blatt! Konstanten anpassen!
' Vs1.0 -LSr:CyWorXxl -cd:20131006e
Sub MusterZInhVerbind()
Const lgQBer As Long = 10, lgZBer As Long = 10, brQBer As Long = 2, _
adQZ1$ = "D1:E1 H1:I1", adZZ1$ = "A1", txMst$ = "X-@-# C-@-# ;------------"
Dim ax As Long, ix As Long, avQDat(3), avMst, avQAdr, avZDat, xv As Variant, _
arQBer As Range
avMst = Split(txMst): avQAdr = Split(adQZ1)
Set arQBer = Union(Range(avQAdr(0)).Resize(lgQBer, brQBer), _
Range(avQAdr(1)).Resize(lgQBer, brQBer))
With WorksheetFunction
avQDat(0) = .Transpose(arQBer.Areas(1).Columns(1).Cells)
avQDat(1) = .Transpose(arQBer.Areas(1).Columns(2).Cells)
avQDat(2) = .Transpose(arQBer.Areas(2).Columns(1).Cells)
avQDat(3) = .Transpose(arQBer.Areas(2).Columns(2).Cells)
End With
ax = LBound(avQDat(0)): ix = ax: ReDim avZDat(UBound(avQDat(0)) - ax)
For Each xv In avQDat(0)
avZDat(ix - ax) = Array(xv, avQDat(1)(ix), avQDat(2)(ix), avQDat(3)(ix)): ix = ix + 1
Next xv
ix = 0
For Each xv In avZDat
avZDat(ix) = Join(Array(Replace(Replace(avMst(0), "@", xv(0)), "#", xv(1)), _
Replace(Replace(avMst(1), "@", xv(2)), "#", xv(3)), avMst(2)), vbLf)
ix = ix + 1
Next xv
With Range(adZZ1).Resize(lgZBer, 1)
.WrapText = True: .Value = WorksheetFunction.Transpose(avZDat)
End With
Set arQBer = Nothing
End Sub
Gruß + schöSo, Luc :-?