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



15871

[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



15871

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

This code copy and paste same sheet :crying: but i try copy from sheet1 to paste sheet2 :banghead:
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

15874


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


15877

[icemail]

Gratitude. Thx for great helping :bow: