Sum a range in VBA With R1C1 Notation (1 Viewer)

[Insane_ai]

I need to get a sum of columnar data from an excel spreadsheet. I can get some data but not all because I won't know which column I'm working in until run time.

I have an idea of converting the numerical column reference to an letter but I'd rather just use R1C1 formatting if possible to simplify the procedure.

'This Fails due to the colon
Debug.Print WorksheetFunction.Sum(R11C & intColumn:R1017C & intColumn)
 
'This works but only sums the first and last of the range.
Debug.Print WorksheetFunction.Sum(R11C & intColumn,R1017C & intColumn)
 
'This also fails as I am using Range improperly but don't understand why yet.
If Range.Select(R11C[intColumn],R1017C[intColumn])

Thanks in advance

 

[Gasman]

I think you can use Range(Cells notation?

    Range(Cells(2, iTCol), Cells(lLastRow, iTCol)).FormulaR1C1 = "=COUNTIF(C1,RC1)"

 

[Insane_ai]

I tried

Debug.Print WorksheetFunction.Sum(Range(Cells(R11C & intColumn), Cells(R1017C & intColumn)))

And it looks like it wants to work but it produces a zero result where is should be a sum in the millions.


Next try is what the Gasman posted above.

 

[Insane_ai]

This Fails with invalid or unqualified reference at "Sum" :

Debug.Print WorksheetFunction.Sum(Range(Cells(R11C & intColumn), Cells(R1017C & intColumn))).FormulaR1C1 = "COUNTIF(C1,RC1)"

 

[Insane_ai]

After reading through this:

https://powerspreadsheets.com/r1c1-...-Full-Row-Or-Column-Using-R1C1-Style-Notation

I still don't have a clue of what to do.

Trying to integrate the suggestion from Gasman is failing.
The latest attempt produces a result of "False"

Debug.Print Range(Cells(R11C & intColumn), Cells(R1017C & intColumn)).FormulaR1C1 = "COUNTIF(C1,RC1)"

 

[Gasman]

That is not what I posted?

Even I am having a problem seeing how it worked, as this was a long time ago, but that code is using (row, column) notation.

My column number was determined by finding the required column (which could move around)

    i = Application.WorksheetFunction.Match("Outstanding Items", Range("A1:AZ1"), 0)
    iTCol = i
    lLastRow = Range("B" & Rows.Count).End(xlUp).Row

then apply that to add formulae

Range(Cells(2, iTCol), Cells(lLastRow, iTCol)).FormulaR1C1 = "=COUNTIF(C1,RC1)"

https://stackoverflow.com/questions/17852071/set-sum-formula-in-excel-using-vba

 

[Insane_ai]

Solution:

Debug.Print objXL.Application.WorksheetFunction.Sum(Range(Cells(11, intColumn), Cells(1017, intColumn)).Value)

I had to reference objXL first to get WorkSheetFunction to work. The rest of the solution comes from Gasman and one of my co-workers assisting me with the .Value at the end.

Thank you to all that helped, I hope this helps someone else later.

 

[Gasman]

I would have just put the Sum formula into the required cell, the same as you would manually, after determining the range involved.

Solution:

Debug.Print objXL.Application.WorksheetFunction.Sum(Range(Cells(11, intColumn), Cells(1017, intColumn)).Value)

I had to reference objXL first to get WorkSheetFunction to work. The rest of the solution comes from Gasman and one of my co-workers assisting me with the .Value at the end.

Thank you to all that helped, I hope this helps someone else later.

 

[Insane_ai]

Thank you Gasman, I should have been more clear that I am not working in a cell. I am manipulating data within a spreadsheet from inside MS Access.

 

[Gasman]

Ah, ok, that makes more sense.

I've only ever inserted formulae using VBA, not made calculations on the fly, so thanks for that.

 

[The_Doc_Man]

The need for using .Value is the rare exception because for an Access object, .Value is the default property. But that is not an Access object. You have opened Excel's innards and thus have to live by those standards. And in Excel, .Value is a special property. So that explains why you needed the .Value property.