Variable Selection Summation

[Guest]

I have a macro that utilizes Excel AND Access, when the final report pops out
of Access, there is some more formatting that needs to be done in excel. The
# of columns vary based on # of locations. I want to do some calculations of
the cells going across and am trying to find a way to have it select the
range and sum for example. Here is what I'm looking at:

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
Part# PQ Loc1OH Loc2OH Loc3OH TOTAL
AAAA 5 1 2 4 7
<-- This Sum is the ?


I can get it to find the last column with:

Dim LastColumn As Long
LastColumn = Range("A1").End(xlToRight).Column
Cells(LastColumn + 1).Select

I cannot get it to select the columns before it to sum them within that
selection.

Also I have it set the MyValue to the number of locations right before that,
so I have that number. I was trying to use IF/Then Statements with looping
around between Line1: Line2: etc. But I couldn't figure out a way to select
the cells over and sum them in the last column.

However, I can't get it to Select Column C,D,E and sum them in F properly.
I could create a hundred code sections and have it jump around in the macro
according to Location numbers, but that would be terribly cumbersome and
long. It will always start with Column C and sum over a # of columns based
on locations. Is there any way to Have it do what I am asking for? Am I
Making Sense? Thanks for any help.

 

[Guest]

does it fit?

Sub test()
Dim LastColumn As Long
Dim LastRow As Long
Dim RowIndex As Long
Const StartColumn = 3
Const StartRow = 2
Dim ColumnDistance As Long

LastColumn = Range("A1").End(xlToRight).Column + 1
ColumnDistance = LastColumn - StartColumn
LastRow = Range("A1").End(xlDown).Row

For RowIndex = StartRow To LastRow
Cells(RowIndex, LastColumn).FormulaR1C1 = "=SUM(RC[-" &
ColumnDistance & "]:RC[-1])"
Next RowIndex
End Sub

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LastCol As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim FirstCol As Long

Set wks = Worksheets("sheet1")

With wks

FirstRow = 2 'data starts in row 2?
FirstCol = 3 'data starts in column C

'I like to come from the right
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'and from the bottom towards the top
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Cells(FirstRow, LastCol + 1).Resize(LastRow - FirstRow + 1, 1) _
.FormulaR1C1 _
= "=sum(rc" & FirstCol & ":rc[-1])"
End With

End Sub

 

[Guest]

Worked Great!!! Thanks. I figured out a solution that took a few seconds
longer for it to complete, your method is much more efficient, Thanks again!!!

Maybe something like:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LastCol As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim FirstCol As Long

Set wks = Worksheets("sheet1")

With wks

FirstRow = 2 'data starts in row 2?
FirstCol = 3 'data starts in column C

'I like to come from the right
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
'and from the bottom towards the top
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Cells(FirstRow, LastCol + 1).Resize(LastRow - FirstRow + 1, 1) _
.FormulaR1C1 _
= "=sum(rc" & FirstCol & ":rc[-1])"
End With

End Sub

I have a macro that utilizes Excel AND Access, when the final report pops out
of Access, there is some more formatting that needs to be done in excel. The
# of columns vary based on # of locations. I want to do some calculations of
the cells going across and am trying to find a way to have it select the
range and sum for example. Here is what I'm looking at:

ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF
Part# PQ Loc1OH Loc2OH Loc3OH TOTAL
AAAA 5 1 2 4 7
<-- This Sum is the ?

I can get it to find the last column with:

Dim LastColumn As Long
LastColumn = Range("A1").End(xlToRight).Column
Cells(LastColumn + 1).Select

I cannot get it to select the columns before it to sum them within that
selection.

Also I have it set the MyValue to the number of locations right before that,
so I have that number. I was trying to use IF/Then Statements with looping
around between Line1: Line2: etc. But I couldn't figure out a way to select
the cells over and sum them in the last column.

However, I can't get it to Select Column C,D,E and sum them in F properly.
I could create a hundred code sections and have it jump around in the macro
according to Location numbers, but that would be terribly cumbersome and
long. It will always start with Column C and sum over a # of columns based
on locations. Is there any way to Have it do what I am asking for? Am I
Making Sense? Thanks for any help.