# Working with Ranges

1. ## Working with Ranges

Good Morning/Afternoon/Evening all,

My question is simple but for some reason I can't figure it out, I'm still new at VBA (been teaching myself for about 3-4 months now). I need to understand more on how to work with ranges. So for example lets say I have two ranges rangea = {1,1,1,1} and rangeb = {1,2,3,4} if I wanted to let's say add them to get a third range rangec = {2,3,4,5} how would I go about doing that? Am I over simplifying this?

Thanks in advance for any help

2. ## Re: Working with Ranges

Your question is confusing to me. Do you intend to add the value of two ranges: A1 + A2? Or are you attempting take two ranges such as A1:A5 & C3:C7 and make a set range out of them. If this is the case look at the use of Union.

3. ## Re: Working with Ranges

Stnkynts,
Sorry for the confusion. I am wanting to (sum, subtract, multiply, divide) two ranges with the result of the action in a third range. I know that I could just loops throught the cells and perform the action with the result being placed in the cell of my choosing, for example for each cell in range("C1:C10") cells.value = cells.offets(-1,0) + cells.offset(-2,0) but I was wondering if there was a way to do it and store it to a new named range that I could then call on and place in another workbook or worksheet depending on what I need to do. I hope this clears up your question.

4. ## Re: Working with Ranges

I have a vague idea of what you are trying to do but without specific information I am not going to be able to assist you much.

Example information:
"I have a range C1:C10 that I am want to add each value its corresponding column B value (same row). Then output that summed value to column C (same row). I would also like to set a range name to each column C cell that has a summed value"

Something like that.

5. ## Re: Working with Ranges

Excel Forum Example.xlsx
I have included a sample file. What I am trying to do is perform the operation in Range 3 without actually having to create a new column and enter the value in each cell. So in essance, I'd like to just define a variable called Range3 and have it equal Range1*Range2.
``Please Login or Register  to view this content.``
Something like that. This way I can take Range3 and input it anywhere I chose based on a few other factors. Is that even possible, or do I have to do a loop then set Range3 as the cells with the value in them?

6. ## Re: Working with Ranges

Ok, I have figured out how to multiply 2 ranges (I have to convert them to array's first), but now when I try to convert it back to a range to put it into the worksheet it skips the first cell. the values of the ranges are as follows range1 = 2,4,6 (located in cells a1:a3) and range2 = 3,6,9 (located in cells b1:b3). When I run the following code it gives me range3 = blank,6,24 (located in cells c1:c3). Anybody have any idea on why it is putting a blank in the first cell and then starting the transpose?

Sub ArrayTest()
Dim a() As Variant
Dim b() As Variant
Dim c() As Variant
'ReDim a(3) Commented out for testing
'ReDim b(3) Commented out for testing
ReDim c(3)
Dim range1 As Range
Dim range2 As Range
Dim range3 As Range
Set range1 = Range("A1:A3")
Set range2 = Range("B1:B3")
Dim j As Variant
a = Application.WorksheetFunction.Transpose(range1)
b = Application.WorksheetFunction.Transpose(range2)

For i = 1 To 3
c(i) = a(i) * b(i)
Next i

Set range3 = Range(Cells(1, 3), Cells(3, 3))

range3 = Application.Transpose(c)

'i = 1 Commented out for testing
'For Each j In range3 Commented out for testing
'Cells(j.Row, 3) = c(i) Commented out for testing
'i = i + 1 Commented out for testing
'Next j Commented out for testing

End Sub

7. ## Re: Working with Ranges

Do you mean like this?

``Please Login or Register  to view this content.``
or did you need something with a loop. or are you trying to put all the values in column C into an array? I'm still confused on what you need.

8. ## Re: Working with Ranges

``Please Login or Register  to view this content.``

Regards, TMS

9. ## looks good

TMS, I'm out grabbing some dinner right now shoo I can't check to verify it well work for me, but it looks like it is exactly what I need. I'll let you know asap. Thanks to everyone who responded. I really appreciate it.

10. ## Re: looks good

That worked perfectly...point of interest though. Is the reason my code didn't work because I didn't realize (until breaking down your code) that an array with 3 elements actually contain 4 subscripts....0,1,2,3?

11. ## Re: Working with Ranges

You're welcome.

By default, the first element in an array has an index of zero (0). When you re dimmed c, you only specified one value, the upper bound, so you actually defined an array with four elements, 0, 1, 2, 3. Then you looped through elements 1 to 3. Then you transposed the first three elements into the range.

New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

12. ## Re: Working with Ranges

TMS,
Much thanks. I was looking for the "Solved" button, but couldn't find it. I will go through and "star" everyone's response. Thanks again for all the help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1