Sum code troubleshoot 1

[ckhunley]

I have the following code which I found to sum the data fromt he current range of cells. I tried asking at tht site, but the thread seems to be long since forgotten. When I run it I get as error saying "Method 'Range' of object '_Gloabal' failes"...I am very new to VBA, so I am really struggling to figure out exactly what this code is doing, or why it isn't working, so I was hoping someone here could figure outn what is wrong with it.

Sub AddingAtTheRear()
Dim c As Range, R As Range, MyStr2 As String
Dim MyStr As String, x As Integer
Set c = ActiveSheet.UsedRange
MyStr = c.SpecialCells(xlCellTypeConstants, 23).Address
MyStr2 = c.SpecialCells(xlCellTypeFormulas, 23).Address
Set R = Range(MyStr & "," & MyStr2)
For x = 4 To R.Columns.Count
MyStr2 = Cells(65536, R.Columns(x).Column).End(xlUp).Address
MyStr = Range(R.Columns(x).Address & ":" & MyStr2).Address
Set c = Range(MyStr)
Range(MyStr2).Offset(2, 0).Formula = "=sum(" & c.Address & ")"
Next
End Sub

When the error appears, it highlights the line with

Set R=Range(...)

Hopefully this makes sense, and someone can help me out.
Thanks,
Chris

 

[SkipVought]

Hi,

That statement is incorrect. You would need to use the UNION function.

Tell me what you are trying to accomplish. Are you summing cells with constants, cells with formulas or both?


Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

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

 

[ckhunley]

I am summing both constants and formulas.
Thanks,
Chris

 

[SkipVought]

Sub AddingAtTheRear()
   Dim c As Range, R As Range, MyRng2 As Range
   Dim MyRng As Range, x As Integer
   Set c = ActiveSheet.UsedRange
   Set MyRng = c.SpecialCells(xlCellTypeConstants, 23)
   Set MyRng2 = c.SpecialCells(xlCellTypeFormulas, 23)
   With MyRng2
      SumRow = .Row + .Rows.Count + 2
   End With
    Set R = Application.Union(MyRng, MyRng2)
   For x = 2 To c.Columns.Count + 1
      Set rng1 = Application.Intersect(Columns(x), R)
        Cells(SumRow, x).Formula = "=sum(" & rng1.Address & ")"
    Next
End Sub

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

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

 

[ckhunley]

Well, unfortunately that isn't working for me either. I get a new error this time saying "Object variable or With block variable not set" Not sure what that means, but hopefully you do. I really appreaciate your help, and hopefully we can get this working.
Thanks again,
Chris

 

[ckhunley]

Forgot to mention, it highlights the following row:

Cells(SumRow, x).Formula = "=sum(" & rng1.Address & ")"

Thanks,
Chris

 

[SkipVought]

      Set rng1 = Application.Intersect(Columns(x), R)
      if not rng1 is nothing then _
        Cells(SumRow, x).Formula = "=sum(" & rng1.Address & ")"

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

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

 

[ckhunley]

Thank you very much Skip. I appreciate the time you put into getting that working. So now I am trying to incorporate that code into my spreadsheet. How does excel define the used range? I got the sum to work on another spreadsheet where I just added a datatable, but It doesn't add anything up in my spreadsheet that contains the data that I am actually trying to work with.
Thanks,
Chris

 

[SkipVought]

There are some anomylies with UsedRange property.

Here are some things that I do when using dynamic ranges.

Keep summary data ABOVE the data table. Reserve empty rows BELOW the table for NEW DATA.

Identify one column that will ALWAYS have a value for each row of data and one row (usually the row containing table headings) that will ALWAYS have a value for any column within the table. I use this column/row for defining one or more dynamic formula/Range names using Insert/Name/Define -- A Name in the Names in workbook and this kind of formula in the RefersTo: Textbox
[tt]
=Offset(Sheet1!$A$1,0,0,counta(Sheet1!A:A),counta(Sheet1!1:1))
[/tt]
faq68-1331 How can I rename a table as it changes size.

I personally more often use the CurrentRegion property. I ALWAYS isolate my tables from any surrounding data, like summaries.

Hope this helps

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

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

 

[ckhunley]

Ok, so after a little guess and check troubleshooting, I figured out that the reason this isn't working in my spreadsheet is because of the column headings. I set up my data in another worksheet and tried the function until I got it to work, changing different aspects(removing blanks, adding/removing text, headings) and I think that the headings are causing my problem. So is there a way to include text in this routine, so that it will still function?

Thanks, you have been most helpful
Chris

 

[ckhunley]

Sorry Skip, I lied, I went back and played with it some more, and it still works with column headings. I can't figure out what it is about my table that it doesn't like. I will let you know if I figure anything else out.
Thanks,
Chris

 

[SkipVought]

With this SUM formula, are you trying to enter a formula that sums a column of data?

Lets say your Table's CurrentRegion were A3:F8. Then the Column A formula, regardless of the constants/formulas therein, would be...
[tt]
=SUM(A3:A8)
[/tt]
The code to produce that formula in row 1 is...

   With ActiveSheet.[A3].CurrentRegion
     For c = .Column To .Column + .Columns.Count - 1
       Set rng = Range(.Cells(1, c), .Cells(.Rows.Count, c))
       ActiveSheet.Cells(1, c).Formula = "=sum(" & rng.Address & ")"
     Next
   End With

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

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

 

[ckhunley]

FINALLY!!! For some reason it was placing the results of the sum in the middle of the data. But anyway, I got it ironed out. Thank you again for your time, you make this sight valuable to people like me.
Thanks,
Chris

 

[SkipVought]

Chris,

A lie is a deliberate deception, with intent to deceive.

Did you deliberately intend to deceive me?

My best guess is that you misspoke or otherwise passed information that was not consistent with the truth, either out of ignorance or haste or whatever.

Words are [red]VERY IMPORTANT![/red] ;-)

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

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