PDA

View Full Version : Vba İnformation About The Array() Cells() Property

icemail
04-07-2016, 01:12 AM
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
04-07-2016, 05:55 AM
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
04-07-2016, 07:48 PM
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
04-08-2016, 05:22 AM
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
04-08-2016, 05:55 AM
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 <<<<<<<<<<<<<<<<<<<

'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
04-08-2016, 01:14 PM
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