Active Cell formula to change automatically

[xls]

hi

when i copy formula from cell and if it has sum formula then i wants to offset column number by 1 and existing formula to override.

EG: if formula in cell A1 is =sum(c1:f1) and if i copy it to A5 cell actual result will be =sum(c5:f5) but i want result in cell to be modified by macro to =sum(e5:h5).

pl help.. thanks in advance..

[xls]

i tried by this code but it doesnt work some how

[VBA]
.
.
.
Dim fcell As Variant
Dim tcell As Variant

If VBA.UCase(Left(ActiveCell.Text, 4)) = "=SUM" Then

fcell = Mid(ActiveCell.Text, 6, 2) 'Mid(ActiveCell.Text, a, b + 1, b - a - 1)
tcell = Mid(ActiveCell.Text, 9, 2) 'Mid(ActiveCell.Text, b + 1, c - b - 1)

fcell = fcell.value.Offset(0, 1)
tcell = tcell.value.Offset(0, 1)

ActiveCell.Value = "=sum(" & fcell.Value & ":" & tcell.Value & ")"
Else
Exit Sub

.
.
.
.
[/VBA]

[Simon Lloyd]

Can i ask why you'd want to do this? seems like overkill for one formula!

[xls]

there is one of requirement by one of my colleague to sum ranges in such a odd fashion .

[xls]

hi any help?

[Kenneth Hobs]

It is unclear if you mean what you say or mean what you show. To offset one column for the last cell in a formula like that you can do this. For cases where you have more than a consecutive range, other tweaks would be needed.
[VBA]Sub Test_OffsetLastFormulaCellByOneColumn()
Range("A1").Formula = "=Sum(c1:f1)"
Range("C1:F1").Formula = "=Column()"
MsgBox "A1=" & Range("A1").Value

Range("A1").Copy Range("A5")
Range("A5").Select
Range("C5:G5").Formula = "=Column()"

OffsetLastFormulaCellByOneColumn
MsgBox ActiveCell.Address & "=" & ActiveCell.Value
End Sub

Sub OffsetLastFormulaCellByOneColumn()
Dim a() As String, p As Integer, f As String
With ActiveCell
If Not .HasFormula Then Exit Sub
f = .Formula
a() = Split(f, ":")
If UBound(a) <> 1 Then
MsgBox .Address & "'s formula does not have a colon.", vbCritical, "Macro Ending"
Exit Sub
End If
p = InStr(f, ":")
.Formula = Left(f, p) & Range(Replace(Right(f, Len(f) - p), ")", "")).Offset(0, 1).Address & ")"
End With
End Sub
[/VBA]

[xls]

hi

i wanted a5 formula to =sum(e5:h5) however macro is returning =SUM(C5:$G$5)

see attached file.

is it possible to do same by designing UDF.

Thanks for efforts

[Kenneth Hobs]

I did what you said:

i wants to offset column number by 1

If you are going to the trouble of a UDF, why not just do the correct formula to begin with?

When I solve problems, tell me your logic, I don't want to guess at what you mean by the example though that is good for testing logic. Usually, more than one example is needed for logic to be derived.

[Simon Lloyd]

Do you mean this formula where the row is always taken from the activecell?, i'm with Kenneth, you're very confusing in what you ask for as you display a different outcome![vba]Activecell.Formula = "=SUM(E" & Activecell.Row & ":H" & Activecell.Row & ")"[/vba]

[xls]

Thanks Simon for your reply & Kenneth you are right unless whole logic is explained solution can not be provided..

see attached file for reference..

I tried with macro but it doesn't work.

Thanks for patience & prompt response.

[Simon Lloyd]

Why do you need to keep changing the formula's with worksheet_change, your sample doesn't help us understand your logic or what you want to achieve.

[Simon Lloyd]

This will do what you want but you CANNOT use it in worksheet_change only worksheet_selection_change or in a standard module[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, MyCell As Range, i As Long
i = 1
Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In rng
MyCell.Formula = "=Sum(Offset(" & MyCell.Offset(0, i).Address & ", 0, 0, 1, 4))"
i = i + 1
Next MyCell
End Sub[/VBA]