Combining 3 columns to produce 1. Creating buckets...adding data values

Hi efarhat,

Keeping the general theory in which your code works (so you understand how it works), I've modified it to loop through what you have. It should work if you change your With portion to:

   With Worksheets("report")
      .Range("C2").FormulaR1C1 = "0 - 3"
      Do Until b > .Range("C65536").End(xlUp).Row
        d = 0
        For Each c In .Range("C" + b + ":F" + b).Cells
             d = d + CDbl(c.Value)
        Next c
        .Range("C" & b).Value = d
        b = b + 1
      Loop
   End With

Also, I would recommend Dim'ming b as Long rather than Integer (if your worksheets are big)

Another way to do it would be using:

Sub AnotherWayToDoIt()
   Dim c As Range
   Dim c2 As Range

   With Worksheets("report")
    .Range("C2").FormulaR1C1 = "0 - 3"
    For Each c In Range(.Range("C3"), .Range("C65536").End(xlUp))
     Set c2 = Range(c, c.Offset(0, 3))
     c = Application.WorksheetFunction.Sum(c2)
    Next c
   End With
End Sub

Just a thought
Matt

I always like shorter code, so it could also be done using:

Sub AnotherWayToDoIt()
 Dim c As Range
 Sheets("report").Range("C2").FormulaR1C1 = "0 - 3"
 For Each c In Range(Sheets("report").Range("C3"), Sheets("report").Range("C65536").End(xlUp))
  c = Application.WorksheetFunction.Sum(Range(c, c.Offset(0, 3)))
 Next c
End Sub

What if I want to do it from select columns?  Also, clear the contents from those columns, so it will only display the new columns?

let me explain in more detail exactly what's going on.  I am currently display all these columns (20).
10 YTM columns and 10 IR columns, sequentially, starting with column C.
like this:
                       0    |    C     l     D    |    E       l     F     |    ...
merged cells-> 1    |        0 - 3        |          4 - 6         |    ...
                       2    | YTM    l    IR    |  YTM    l     IR    |    ...
                       3    | data    | data   | data     |  data   |
                       4    | data    | data   | data     |  data   |

is this at all possible?

Kind of, although you may still have to do a little bit of manual work.  

The following sub will sum up however many columns you have selected (starting at row 3), and place the sum in the leftmost column.  As an example, this will work if you have columns C,E,G,I,K selected, or if you have X:Z selected, whatever columns you have it will sum them up and put them in the left most column selected.  It will then delete all selected columns except for the leftmost column selected. You could do this for each of your ranges, both in YTM and IR.  The manual work you will have to do is put the IR columns next to the YTM, and merge the heading cells.  
NOTE: the macro below will not put the "0 - 3" heading in, as I'm under the impression you're going to be doing this to more than just 0-3.
If I have not understood fully what you're asking, please let me know, and explain what I'm missing.  Here's the sub:

Sub SumNonAdjacentSelectColumns()
 Dim r As Long, fC As Integer, cols As Range
 If Selection.Columns.Count = 1 Then Exit Sub
 fC = Selection.Column
 For r = 3 To Cells(65536, fC).End(xlUp).Row '3 is the first row that contains data to sum
  Cells(r, fC) = Application.WorksheetFunction.Sum(Intersect(Rows(r), Selection.Cells))
 Next r
 For Each cols In Selection.Areas
  If Intersect(Columns(fC), cols) Is Nothing Then
   cols.EntireColumn.Delete
  Else
   If cols.Columns.Count > 1 Then
    For r = cols.Column To cols.Column + cols.Columns.Count - 1
     If r <> fC Then Columns(r).EntireColumn.Delete
    Next r
   End If
  End If
 Next cols
 Intersect(Columns(fC), Selection.Cells).Select
End Sub

mvidas,

i'm not sure what the code you have posted is doing.    I'm trying to understand it so I can modify it to accompany worksheet layout.  

may i email a simplified version of what it's doing and what i am trying to accomplish? i will double the points if you like.  I really appreciate the help.  As you can see, i'm not looking strictly looking for code but more of an explanation on how to go about doing this.  Realizing though, having code does explain a lot.  Many thanks.

email me at <email removed - Bingie EE PE> to prevent from posting your email addy.

Unfortunately EE frowns upon emailing files like this, as it takes away changes other people might have of helping.
It sounds like you have 10 columns of YTM then 10 columns of IR, right? And you want to take the 20 columns you have and make them into 8, 2 for each of the 4 time spans?    I could write code that would do that, but if you want to understand how it works (which I always need to), I could show you how to modify the first piece of code I wrote (that used the theory in your code) to suit your needs.  But it sounds like I'm still missing the point a bit, let me think about this and see what will work best (I need to step away from the computer for a few minutes anyways)

exactly.  

i'll mess around with your code a little bit and see if i can get anywhere.

mvidas,

maybe this may help some.  i tried explaining this before but not sure i did a good job of that.  pictures say 1000 words:

YTM  YTM  YTM  YTM  YTM  .... | |  IR   IR   IR   IR ...
0Y     1Y      2Y     3Y    4Y    .... | |  0Y  1Y   2Y  3Y ..

these are displayed in order and I need to group them. One YTM and one IR for the bucket 0-3 and so on.   Does that help?      

1000 thank yous!
 

i doubled the pts btw.

Excellent!  That's worked wonderfully!  Thank you.

Quck question: do you know how to define ranges in excel? not using vba, through excel menu?

As in a named range?

Sure, there are two ways to do it, both assume the range you want to name is already selected.

First way: click in the white dropdown box to the left of your formula bar, type in the range name you want, and press enter.

Second way: Go to Insert, then Name, then Define.  Type the name you want in the top box, and click Add.

Should be all set!

ahh, yes! am i able to revise my posting that has my email address so i can remove it?

i thought i would be able to find it on my own but didn't see it.  can i define named ranges in code?

what do you recommend as a good resource?

You can't edit the posting yourself, but if you send an email to the Page editor, they can edit it for you.  The page editor is always listed on the lefthand side of the page, near the top.  In VB the page editor is Bingie and his email address is on his profile ( https://www.experts-exchange.com/M_1151875.html ).  Send him an email with your member name, a link to the question, and what you want to do.  For the question link, use
https://www.experts-exchange.com/questions/21088546/Combining-3-columns-to-produce-1-Creating-buckets-adding-data-values.html#11767351
As it links directly to that comment.

To define named ranges in code, use syntax like:
  ActiveWorkbook.Names.add Name:="TheName", RefersTo:="=Sheet1!$A$1:$C$3"
Remember to use the $ symbols too, otherwise the RefersTo is only in reference to the active cell at the time.

As far as resources go, there are many good ones out there.  I still think EE is the best, as pretty much any question that could be asked has been asked.

First, I'd say a question like this should go in the Excel Area rather than the VB area:
https://www.experts-exchange.com/Applications/MS_Office/Excel/

A great resource is John Walkenbach's page, he is a true Excel guru:
http://www.j-walk.com/ss/excel/

In regards to sending email from excel, my favorite site is:
http://www.rondebruin.nl/sendmail.htm

The email address in my profile is from another great VBA resource site.

Google is another great one, especially when used in conjunction with EE here.  In google, enter your search like:
  site:https://www.experts-exchange.com excel named ranges
That restricts the pages to be EE pages, any you'll almost always find what you're looking for that way.

Gracias.

De nada