Looped naming of cell ranges

[ckhunley]

I am trying to use VBA to sum a certain selection of cells. But I need it to input the formula with cell reference into Excel, not just the value it calculated. The only way I cn come up with is to name the selected range and use ="=SUM(SelectedRange)" The problem is that I need to be able to do this several times, and I can't rename the ranges. Is there a way to work a loop in so that it changes the range name (to say..SelectedRange2, SelectedRange3 and so on...). Or maybe there is a better way to do this.
Thanks in advance for the help
Chris

 

[SkipVought]

Chris,

If you have Names Ranges named SelectedRange1, SelectedRange2, SelectedRange3...
then you can do something like this...

for each n in names
  if left(n.name, 13) = "SelectedRange" then
    sFormula = "=SUM(" & n.name & ")"
  end if
next

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ckhunley]

What I am struggling with is actually naming the ranges so that I can reference them. Bascially what I have is a database that I filter using a macro, the results are pasted into another worksheet, and then I want to subtotal a certain column. Here is the code that I have right now to Sum the range(which has a different number of values depending on the filter criteria.

Range("L65536").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Dim dppsisum As Range
Set dppsisum = Selection
Range("L65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Formula = Application.WorksheetFunction.sum(dppsisum)

(I know the code is probably very sloppy, but this is the first thing I have ever done on VBA so I am trying to learn as I go...)

The problem is that this only puts a value into the spreadsheet, and I want it to be a formula which would update if the user went back and manully changed entries in this section.

I tried one other thing(not shown in this code)...my thought was to name the section in Excel, so that I could use the ="SUM("")" function, but I run into trouble when the next filter is run and a new dataset is pasted below the previous. It names my new selection the same name as the previous, so my Sum is the same as the previous, rather than changing to represent the new dataset. Sorry if this is a horrible explanation of what I am trying to do, but hopefully you can translate it.
Thanks,
Chris

 

[SkipVought]

    Dim dppsisum As Range
    With ActiveSheet.UsedRange
        r1 = .Row
        r2 = r1.Rows.Count = 1
    End With
    Set dppsisum = Range(Cells(r1, "L"), Cells(r2 + .Rows.Count - 1, "L"))
    Cells(r1 - 1, "L").Value = Application.Sum(dppsisum)

If you wanted to do this for columns 5 to 9

    Dim dppsisum As Range
    With ActiveSheet.UsedRange
        r1 = .Row
        r2 = r1.Rows.Count = 1
    End With
    for c = 5 to 9
      Set dppsisum = Range(Cells(r1, "L"), Cells(r2 + .Rows.Count - 1, c))
      Cells(r1 - 1, c).Value = Application.Sum(dppsisum)
    next

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ckhunley]

Well I tried implementing that code, but when it runs I get an error saying invalid qualifyer, and it highlights the r1 in the line:

r2 = r1.Rows.Count = 1

Also, if that worked for me, it would still only return a value for the sum, rather than a formula with cell references. correct? I need it to be an active formula so that is can be updated if changes are made.
Thanks,
Chris

 

[SkipVought]

sorry,

r2 = r1 + .rows.count - 1

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ckhunley]

Unfortunately, since I have no idea what this bit of code is doing, I am unable to troubleshoot it. IT is now giving me an Invalid or unqualified reference error in:

Set dppsisum = Range(Cells(r1, "L"), Cells(r2 + .Rows.Count - 1, "L"))

It highlights the .Rows term...And as I asked before, will this just pop the result into Excel instead of the range referenced formula?

Thanks again for your help Skip.
Chris

 

[SkipVought]

Chris,

I have to appologize for not giving my responses to you a closer look. Please forgive me.

    Dim dppsisum As Range
    With ActiveSheet.UsedRange
        r1 = .Row                  'first row in used range
        r2 = r1 + .Rows.Count = 1  'last row in used range
    End With
    Set dppsisum = Range(Cells(r1, "L"), Cells(r2, "L"))
'puts the column total in the row just above the FORMER used range
    Cells(r1 - 1, "L").Value = Application.Sum(dppsisum)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244