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
Last edited by troussis; 04-10-2014 at 09:29 PM. Reason: Solved
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.
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.
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.
Last edited by stnkynts; 04-10-2014 at 01:59 PM.
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.
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?
Please Login or Register to view this content.
Last edited by Leith Ross; 04-10-2014 at 04:00 PM. Reason: Added Code Tags
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
Do you mean like this?
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.Please Login or Register to view this content.
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
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.
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?
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.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
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.
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks