Loop through range 1

[HobbitK]

Hi everyone ..
I am lost on how to start this as I am fairly new to VBA, and I know someone out there has my answer. I searched the Forum and FAQ area and did not find much help.
Question...
I have 5 named Ranges on a spreadsheet that are all different sizes. I need to loop through each cell of each range and assign the value of that cell to a variable, so I can perform some calculations on those numbers. But every thing I have tried has caused Run-time or syntax errors.
Thanks for any help or guideance you can offer.
Michael

 

[Zathras]

You should be able to do something like this:

[blue]

Option Explicit

Sub test()
Dim r As Range
Dim c As Range
Dim nSum As Integer

  Set r = Union(Range("Range1"), Range("Range2"), Range("Range3"), Range("Range4"), Range("Range5"))
  For Each c In r
    nSum = nSum + c.Value
  Next c
  MsgBox nSum
  Set r = Nothing
End Sub

[/color]

 

[HobbitK]

Zathras,
Thanks ... have a Star

I got the first loop to work after I posted the question by doing this ..

Sub Test()
Dim vFirstVariable as Variant

For Each vFirstVariable in Range("Range1&quot
vFirstVariable = vFirstVariable.Value
Next vFirstVariable
End Sub

That works great
But when I added a second one in this fashion, I got Errors

Dim vFirstVariable as Variant
Dim vSecondVariable as variant

For Each vFirstVariable in Range("Range1&quot
vFirstVariable = vFirstVariable.Value

For Each vSecondVariable in Range("Range2&quot
vSecondVaraible = vSecondVariable.Value

Do something
Next vFirstVariable
Next vSecondVariable

Thought being ... Get the first value, get the second value, Do Something, Get new values, but Excel demands I play by its rules, not mine.
I will try your code and see how Excel decides to make my evening difficult.
Thanks again
Michael

 

[Zathras]

A couple of problems with your code:

Dim vFirstVariable as Variant
For Each vFirstVariable in Range("Range1&quot

vFirstVariable should be Dimmed as Range to use the For each syntax.


vFirstVariable = vFirstVariable.Value

[blue]vFirstVariable.Value[/color] implies that vFirstVariable is a Range while [blue]vFirstVariable = [/color] implies that vFirstVariable is a simple variable. A bit of a contradiction.

For Each vFirstVariable in Range("Range1&quot
For Each vSecondVariable in Range("Range2&quot
Do something
Next vFirstVariable
Next vSecondVariable

The "Next" statements are in the wrong order. Even if they were reversed however, you would still not get the results you want. For example if "Range1" has 10 elements and "Range2" has 10 elements "Do something" would be done 100 times, not 20.

 

[HobbitK]

Zathras,
Thanks for the lesson. This is still not quite working the way I need it, but I have a couple hours available to continue attampting to figure this out myself. i may end up having to request some further help.
Stay tuned and Thanks for all you have done!
Michael

 

[HobbitK]

Zathras,
The more I play with VBA the more I realize I don't know

I would ideally like to loop through these ranges and have the value of each cell assigned to an array so I can manipulate it later. So far, all my feeble attempts has done nothing but teach Excel how many error codes it contains, because I have sure seen alot of them!!
The problems I am having (so far) is that each Range is different lengths and additions will be made to the ranges often, and secondly, that I need to ReDim Preserve the values in the Array(s)and get them into Long variables.
Excel help has so far not gotten me very far.
Any ideas?
Thanks
Michael

 

[Zathras]

Without a little more detail on what you are trying to do, all I can offer is some more generalized code. Below is one way to fill an arrray from multiple ranges. Note that if any of the ranges overlap, using Union will see to it that the overlapped cells will only be referenced once.
[blue]

Option Explicit

Sub test()

[green]

' Demo loading array from multiple ranges

[/color]

Dim rng As Range
Dim c As Range
Dim i As Integer
Dim laValues() As Long

  Set rng = Union(Range("Range1"), Range("Range2"), Range("Range3"), Range("Range4"), Range("Range5"))
  ReDim laValues(rng.Cells.Count)
  For Each c In rng
    laValues(i) = c.Value
    i = i + 1
  Next c
  MsgBox "laValues(4) = " & laValues(4)
  Set rng = Nothing
End Sub

[/color]

 

[HobbitK]

Zathras,
Thank-you so much.
I was close to having it, but did not even think of this one line ..
ReDim laValues(rng.Cells.Count)
I will try this out in a little bit.
Michael