Working with ranges in arrays... or an introduction to arrays

[Guest]

I understand arrays from several posts and the excel help but I haven't
really seen if you can fill arrays with ranges without having to use some
form of loop.

For example I wish to have an array that represents to column ranges...

A2:A30 and B2:B30

Also once the array is created I would like to do a repeat for each value in
the range A2:A30 to compare to a range in a worksheet say T1:T3500 if there
is a match then the value in array B2:B30 is added to the value in the column
S in matching row of the T1:T3500

Example. (say S5 =27)

if A2 = 3456 and B2 = 12 and T5 = 3456 then 12 is added to the value in S5
making the new value in S5 = 39

Any suggestions?

thanks,

 

[halimnurikhwan]

Glen,

Why you don't use Worksheets funtion CountIf ?
If you still want to do this by VBA, first thing you have define is:
Logical value that represent True based criterion you defined befor
loop
ie:
sub test()
For r = 2 to 3500 ' it will take much time
if range("A" & r ).value = 3456 and range("R" & r + 3).value =
3456 then _
range("S" & r + 3).value = range("B" & r ).value
Next r
end sub

Thanks,

Halim

Glen menuliskan:

 

[halimnurikhwan]

Glen,
Sorry miss one thing
Chage range "R" to "T"
like :
Sub test()
For r = 2 To 3500
If Range("A" & r).Value = 3456 And _
Range("T" & r + 3).Value = 3456 Then _
Range("S" & r + 3).Value = Range("B" & r).Value
Next r
End Sub

Rgds,

Halim

Glen menuliskan:

 

[Leo Heuser]

I understand arrays from several posts and the excel help but I haven't
really seen if you can fill arrays with ranges without having to use some
form of loop.

For example I wish to have an array that represents to column ranges...

A2:A30 and B2:B30

Also once the array is created I would like to do a repeat for each value
in
the range A2:A30 to compare to a range in a worksheet say T1:T3500 if
there
is a match then the value in array B2:B30 is added to the value in the
column
S in matching row of the T1:T3500

Example. (say S5 =27)

if A2 = 3456 and B2 = 12 and T5 = 3456 then 12 is added to the value in S5
making the new value in S5 = 39

Any suggestions?

thanks,

Hi Glen

Here's one way:

First the values of the two ranges are put into two arrays,
FirstRangeValue and SecondRangeValue. Then the comparison
is effectuated in the two loops, and at last the changed array
SecondRangeValue is inserted into SecondRange.

Sub RangeToArray()
'Leo Heuser, Sept. 2006
Dim Counter As Long
Dim Counter1 As Long
Dim FirstRange As Range
Dim FirstRangeValue As Variant
Dim SecondRange As Range
Dim SecondRangeValue As Variant

Set FirstRange = Worksheets("Sheet2").Range("A2:B30")
Set SecondRange = Worksheets("Sheet2").Range("S1:T3500")

FirstRangeValue = FirstRange.Value
SecondRangeValue = SecondRange.Value

For Counter = 1 To UBound(FirstRangeValue, 1)
For Counter1 = 1 To UBound(SecondRangeValue, 1)
If FirstRangeValue(Counter, 1) = _
SecondRangeValue(Counter1, 2) Then
SecondRangeValue(Counter1, 1) = _
SecondRangeValue(Counter1, 1) + _
FirstRangeValue(Counter, 2)
End If
Next Counter1
Next Counter

SecondRange.Value = SecondRangeValue

End Sub

 

[Guest]

HI Leo,

Thanks for the solution... but I was wondering two things.

1.if the range of cells are not be side by side ie A2:A30 and N2:N20 so how
could this be loaded into an array?

2. the worksheets are two separate excel files using sheet 1 of each. (I'm
still learning the context between files - workbooks - sheets so please bear
with me).

thanks,

 

[Leo Heuser]

HI Leo,

Thanks for the solution... but I was wondering two things.

1.if the range of cells are not be side by side ie A2:A30 and N2:N20 so
how
could this be loaded into an array?

2. the worksheets are two separate excel files using sheet 1 of each. (I'm
still learning the context between files - workbooks - sheets so please
bear
with me).

thanks,

Hi Glen

Here's an example. I hope, you can spot the pattern.
Both workbooks must be open, and the code may
be placed in either one.

Sub RangeToArray2()
'Leo Heuser, 10 Sept. 2006
Dim Counter As Long
Dim Counter1 As Long
Dim FirstRange As Range
Dim FirstRangeValue As Variant
Dim SecondRange As Range
Dim SecondRangeValue As Variant
Dim ThirdRange As Range
Dim ThirdRangeValue As Variant


Set FirstRange = Workbooks("Book1.xls"). _
Worksheets("Sheet1").Range("A2:A30")
Set SecondRange = Workbooks("Book1.xls"). _
Worksheets("Sheet1").Range("N2:N30")
Set ThirdRange = Workbooks("Book2.xls"). _
Worksheets("Sheet1").Range("S1:T3500")

FirstRangeValue = FirstRange.Value
SecondRangeValue = SecondRange.Value
ThirdRangeValue = ThirdRange.Value

For Counter = 1 To UBound(FirstRangeValue, 1)
For Counter1 = 1 To UBound(ThirdRangeValue, 1)
If FirstRangeValue(Counter, 1) = _
ThirdRangeValue(Counter1, 2) Then
ThirdRangeValue(Counter1, 1) = _
ThirdRangeValue(Counter1, 1) + _
SecondRangeValue(Counter, 1)
End If
Next Counter1
Next Counter

ThirdRange.Value = ThirdRangeValue

End Sub