+ Reply to Thread
Results 1 to 12 of 12

Working with Ranges

  1. #1
    Registered User
    Join Date
    02-07-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    2013
    Posts
    21

    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
    Last edited by troussis; 04-10-2014 at 09:29 PM. Reason: Solved

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    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. #3
    Registered User
    Join Date
    02-07-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    2013
    Posts
    21

    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. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    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.
    Last edited by stnkynts; 04-10-2014 at 01:59 PM.

  5. #5
    Registered User
    Join Date
    02-07-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    2013
    Posts
    21

    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?
    Last edited by Leith Ross; 04-10-2014 at 04:00 PM. Reason: Added Code Tags

  6. #6
    Registered User
    Join Date
    02-07-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    2013
    Posts
    21

    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. #7
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    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. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,762

    Re: Working with Ranges

    Please Login or Register  to view this content.

    Regards, TMS

  9. #9
    Registered User
    Join Date
    02-07-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    2013
    Posts
    21

    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. #10
    Registered User
    Join Date
    02-07-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    2013
    Posts
    21

    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. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,762

    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.



    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.

  12. #12
    Registered User
    Join Date
    02-07-2014
    Location
    Chicago, Illinois
    MS-Off Ver
    2013
    Posts
    21

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] countifs working on extended ranges and different sheets not working
    By etaf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2013, 02:23 PM
  2. Working with two ranges
    By udigold1 in forum Excel General
    Replies: 1
    Last Post: 08-27-2008, 10:04 AM
  3. Working with ranges
    By gtorres in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2007, 12:11 PM
  4. Working with ranges
    By okaderli in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2007, 10:12 PM
  5. Working with Ranges
    By somefred in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2006, 10:04 PM

Bookmarks

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