ActiveFormula R1C1
 

Hi,

i am trying to sum a range of values from coloumn H to Column i. i is a
variable, and it depends on the input no for Cloumn F. If column F is 6, that
means that values need to be summed up from Column H to M( 6 columns
counting from H as a reference). but this does not work, can anyone advise
anything wrong with my code??? The result will be returned in Column G.

pls see code below:

Sub Book1()
Application.ScreenUpdating = False

Dim CurrentRow As Integer
Dim NumOfRows As Integer
Dim i As Integer


NumOfRows = Cells(Rows.Count, 1).End(xlUp).Row

For CurrentRow = 2 To NumOfRows
i = Cells(CurrentRow, "F").Value

With ActiveSheet
.Range("G2").Select
.Range("G" & CurrentRow).FormulaR1C1 _
= "=SUM(RC[1]:RC[i])"
End With
Next


Application.ScreenUpdating = True
End Sub

ActiveFormula R1C1
 

"Junior728" wrote:
Hi,

i am trying to sum a range of values from coloumn H to Column i. i is a
variable, and it depends on the input no for Cloumn F. If column F is 6, that
means that values need to be summed up from Column H to M( 6 columns
counting from H as a reference). but this does not work, can anyone advise
anything wrong with my code??? The result will be returned in Column G.

Why do you need a macro for this? Just put
=SUM(OFFSET(H1,0,0,1,F1))
in G1 and copy down.

-=Wim=-

ActiveFormula R1C1
 

thanks Wim, you help me to find the right function in excel. At first, i dont
know what function can be used, and therefore i resort to VBA...

"Wim SKW" wrote:

"Junior728" wrote:
Hi,

i am trying to sum a range of values from coloumn H to Column i. i is a
variable, and it depends on the input no for Cloumn F. If column F is 6, that
means that values need to be summed up from Column H to M( 6 columns
counting from H as a reference). but this does not work, can anyone advise
anything wrong with my code??? The result will be returned in Column G.

Why do you need a macro for this? Just put
=SUM(OFFSET(H1,0,0,1,F1))
in G1 and copy down.

-=Wim=-