PDA

View Full Version : Active Cell formula to change automatically



xls
09-12-2012, 11:06 AM
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..
:beerchug:

xls
09-12-2012, 11:19 AM
i tried by this code but it doesnt work some how


.
.
.
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

.
.
.
.

Simon Lloyd
09-12-2012, 11:59 AM
Can i ask why you'd want to do this? seems like overkill for one formula!

xls
09-12-2012, 09:49 PM
there is one of requirement by one of my colleague to sum ranges in such a odd fashion .

xls
09-13-2012, 06:47 AM
hi any help?

Kenneth Hobs
09-13-2012, 09:20 AM
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.
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

xls
09-13-2012, 09:58 AM
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
09-13-2012, 10:19 AM
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
09-13-2012, 11:08 AM
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!Activecell.Formula = "=SUM(E" & Activecell.Row & ":H" & Activecell.Row & ")"

xls
09-13-2012, 09:30 PM
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. :friends: :beerchug:

Simon Lloyd
09-13-2012, 11:56 PM
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
09-14-2012, 12:17 AM
This will do what you want but you CANNOT use it in worksheet_change only worksheet_selection_change or in a standard modulePrivate 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