Vba İnformation About The Array() Cells() Property

[icemail]

hi

Need more information about the array() cells() property. Can i use both array & cells together ?

example

arr = Array(cells(1,1))

increased step by step
vol 1

arr = Array(cells(1,1)),cells(1,2),cells(1,3))

vol 2 with combine range(cell())

arr = Array(Range(Cells(1, 1), Cells(1, 11)),Range(Cells(2, 1), Cells(Cells(2,11))

vol 3 with xl end up,down,left,right

arr = Array(Range(Cells(1, 1), Cells(1, 11)),Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 11))

vol 4 with variables

var1 = Cells(1, 11).Column
var2 = Cells(1, 1).End(xlToRight).Column
var3 = Cells(1, 1).End(xlDown).Row
var4 = Cells(1, 1).End(xlToRight).Column - 8

Set rng1 = Range(Cells(1, 1), Cells(1, 11))
Set rng2 = Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 11))
Set rng3 = Range(Cells(1, var4), Cells(1, var2))
Set rng4 = Range(Cells(2, var4), Cells(var3, var2))


arr = Array(rng1,rng2,rng3,rng4)

vol 5 combine with 2 difrent sheets and 2 array

arr1 = Array(rng1,rng2,rng3,rng4)
arr2 = Array(Range(Cells(1, 1), Cells(1, 11)),Range(Cells(2, 1), Cells(2, 11)),Range(Cells(1, 15), Cells(1, 15)),Range(Cells(2, 15), Cells(2, 15))


for i= array low to high
with sh1
arr1(i).copy
end with

with sh2
'copy sh1 arr1 range after sh2 arr2 area paste
arr2(i).paste
end with
next
....

thx everybody

[Paul_Hossler]

Yes .. sort of ... maybe

Depend on how you do it

Below arr1 and arr2 are Variants and have an Array assigned to them with Range objects and strings and Long

The Watch window shows what is in each 'slot'

Option Explicit
Sub aaa()
    Dim arr1 As Variant, arr2 As Variant
    Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
    Dim i As Long
    Set rng1 = Range("A1:B2")
    Set rng2 = Range("A3:B4")
    Set rng3 = Range("A5:B6")
    Set rng4 = Range("A7:B8")
    arr1 = Array(rng1, rng2, rng3, rng4, "ABCDEF", 123456)
    arr2 = Array(Range(Cells(1, 1), Cells(1, 11)), Range(Cells(2, 1), Cells(2, 11)), Range(Cells(1, 15), Cells(1, 15)), Range(Cells(2, 15), Cells(2, 15)))
    
    MsgBox arr1(0).Cells(2, 2).Value
    MsgBox LCase(arr1(4))
 
    For i = LBound(arr1) To UBound(arr1)
        With Worksheets("sheet1")
            arr1(i).Copy
        End With
         
        With Worksheets("sheet2")
             'copy sh1 arr1 range after sh2 arr2 area paste
'            arr2(i).Paste
        End With
    Next i
End Sub

Capture.JPG

[mikerickson]

hi

Need more information about the array() cells() property. Can i use both array & cells together ?

example

arr = Array(cells(1,1))

increased step by step
vol 1

arr = Array(cells(1,1)),cells(1,2),cells(1,3))

vol 2 with combine range(cell())

arr = Array(Range(Cells(1, 1), Cells(1, 11)),Range(Cells(2, 1), Cells(Cells(2,11))

vol 3 with xl end up,down,left,right

arr = Array(Range(Cells(1, 1), Cells(1, 11)),Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 11))

vol 4 with variables

var1 = Cells(1, 11).Column
var2 = Cells(1, 1).End(xlToRight).Column
var3 = Cells(1, 1).End(xlDown).Row
var4 = Cells(1, 1).End(xlToRight).Column - 8

Set rng1 = Range(Cells(1, 1), Cells(1, 11))
Set rng2 = Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 11))
Set rng3 = Range(Cells(1, var4), Cells(1, var2))
Set rng4 = Range(Cells(2, var4), Cells(var3, var2))


arr = Array(rng1,rng2,rng3,rng4)

vol 5 combine with 2 difrent sheets and 2 array

arr1 = Array(rng1,rng2,rng3,rng4)
arr2 = Array(Range(Cells(1, 1), Cells(1, 11)),Range(Cells(2, 1), Cells(2, 11)),Range(Cells(1, 15), Cells(1, 15)),Range(Cells(2, 15), Cells(2, 15))


for i= array low to high
with sh1
arr1(i).copy
end with

with sh2
'copy sh1 arr1 range after sh2 arr2 area paste
arr2(i).paste
end with
next
....

thx everybody

What happened when you tried it?

[icemail]

Yes .. sort of ... maybe

Depend on how you do it

Below arr1 and arr2 are Variants and have an Array assigned to them with Range objects and strings and Long

The Watch window shows what is in each 'slot'

Option Explicit
Sub aaa()
    Dim arr1 As Variant, arr2 As Variant
    Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
    Dim i As Long
    Set rng1 = Range("A1:B2")
    Set rng2 = Range("A3:B4")
    Set rng3 = Range("A5:B6")
    Set rng4 = Range("A7:B8")
    arr1 = Array(rng1, rng2, rng3, rng4, "ABCDEF", 123456)
    arr2 = Array(Range(Cells(1, 1), Cells(1, 11)), Range(Cells(2, 1), Cells(2, 11)), Range(Cells(1, 15), Cells(1, 15)), Range(Cells(2, 15), Cells(2, 15)))
    
    MsgBox arr1(0).Cells(2, 2).Value
    MsgBox LCase(arr1(4))
 
    For i = LBound(arr1) To UBound(arr1)
        With Worksheets("sheet1")
            arr1(i).Copy
        End With
         
        With Worksheets("sheet2")
             'copy sh1 arr1 range after sh2 arr2 area paste
'            arr2(i).Paste
        End With
    Next i
End Sub

Capture.JPG

Thx for the great sample. This code works perfectly for me. And i have another questions.

This code copy and paste same sheet but i try copy from sheet1 to paste sheet2
example code

Sub test()

Dim arr1 As Variant
Dim arr2 As Variant

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range

Set rng1 = Cells(1, 1)
Set rng2 = Cells(1, 2)

Set rng3 = Cells(1, 3)
Set rng4 = Cells(1, 4)

arr1 = Array(rng1, rng2)
arr2 = Array(rng3, rng4)

For i = LBound(arr1) To UBound(arr1)
With Worksheets("sheet1")
arr1(i).Copy
End With

With Worksheets("sheet2")
arr2(i).PasteSpecial xlPasteValues
End With
Next

End Sub

And another question how can i display this points

Capture.JPG


thx

[Paul_Hossler]

Some of your macro doesn't work the way I think you think it works. Basically the elements of the 2 arrays are ranges and they are objects with properties

Look at my comments/suggestions in test() and then check out the TryThis() sub

Option Explicit

Sub test()
     
    Dim arr1 As Variant
    Dim arr2 As Variant
    Dim i As Long
     
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
     
    Set rng1 = Cells(1, 1)
    Set rng2 = Cells(1, 2)
    Set rng3 = Cells(1, 3)
    Set rng4 = Cells(1, 4)
    
    'rng1-4 as objects on whatevere the Activesheet happens to be <<<<<<<<<<<<<<<<<<<
    MsgBox rng1.Address(1, 1, 1, 1)
    MsgBox rng2.Address(1, 1, 1, 1)
    MsgBox rng3.Address(1, 1, 1, 1)
    MsgBox rng4.Address(1, 1, 1, 1)
    
    
    'arr1 and arr2 still contain objects on Activesheet <<<<<<<<<<<<<<<<<<<<
    arr1 = Array(rng1, rng2)
    arr2 = Array(rng3, rng4)
     
    'The With/End are not needed and don't do what I think you think they do
    For i = LBound(arr1) To UBound(arr1)
'        With Worksheets("sheet1")
            arr1(i).Copy
'        End With
         
'        With Worksheets("sheet2")
            'this says to paste rng3 on sheet1 into rng3 also on sheet1
            arr2(i).PasteSpecial xlPasteValues
'        End With
    Next
     
End Sub


Sub TryThis()
     
    Dim arr1 As Variant, arr2 As Variant
    Dim i As Long
     
    Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
         
    Set rng1 = Worksheets("Sheet1").Cells(1, 1) '   Sheet1
    Set rng2 = Worksheets("Sheet1").Cells(1, 2)
    Set rng3 = Worksheets("Sheet2").Cells(1, 3) '   Sheet2
    Set rng4 = Worksheets("Sheet2").Cells(1, 4)
    
    arr1 = Array(rng1, rng2)
    arr2 = Array(rng3, rng4)
     
    For i = LBound(arr1) To UBound(arr1)
        arr1(i).Copy
        arr2(i).PasteSpecial xlPasteValues
    Next
     
End Sub

[Paul_Hossler]

And another question how can i display this points

That's the 'Watch Window'. Available from the [View] menu in the editor, or toolbar icon if you show additional toolbars


Several options, but here's 2 helpful links

http://www.cpearson.com/excel/DebuggingVBA.aspx

https://www.youtube.com/watch?v=19JTjCtFPeI


Capture.JPG

[icemail]

Gratitude. Thx for great helping