macro code reqd

[webmax]

Hi
i am attaching the macro code
i have wrritten the macro code for auto sum the values. In that the dollar symbol is appearing i want to remove the static which is dollar symbol.
For example in the B column=SUM(B$2:B15) i want this as SUM(B2:B15)
and also help in writing the Macro code for Fill Right the total from B Range to F Range.
regards
Shahul

 

[GCExcel]

Hi webmax
Maybe like this?

Sub autosum()
    Dim R As Long
    lrow = Range("B1").End(xlDown).Row
    Range("F1").End(xlDown).Offset(1).Formula = "=SUM(R[-" & (lrow - 1) & "]C:R[-1]C)"
    Range("B1").End(xlDown).Offset(1).Formula = "=SUM(R[-" & (lrow - 1) & "]C:R[-1]C)"
End Sub

 

[Smallman]

Webmax

I like to come from the bottom up. If there is ever a blank cell in your dataset the bottom up method works nicely.

Option Explicit
Sub autosum1()
Dim lr As Long
lr = Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("B" & lr & "," & "F" & lr).Value = "=SUM(R[-" & (lr - 1) & "]C:R[-1]C)"
End Sub

Try and use Option Explicit, it will help if you put the wrong variable in your VB coding (R as long)

Take care

Smallman

 

[webmax]

how to refill the formula to right.

 

[Debraj]

Hi Shahul

Provided code by Smallman is fully capable to fill data in Right..
Just need a Small Modification.. If macrcus allows me ...

Range("B" & lr & ":" & "F" & lr).Value = "=SUM(R[-" & (lr - 1) & "]C:R[-1]C)"