Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formual Issue
Hi,
Simple formula to insert a SUM. Range("G22").Select Selection.End(xlDown).Select ActiveCell.Offset(2, 0).Select ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)" But it returns in Excel......=SUM('G5':G124) which doesn't work. Any idea? Thanks John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formual Issue
Hi John
I can't see why you are selecting G22, but wanting the formula to run from G5. Try the following Sub test4() Dim lr As Long Range("G5").Select lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3).Select ActiveCell.Formula = "=Sum(" & "G5:G" & lr & ")" End Sub Amend to suit -- Regards Roger Govier "John" wrote in message ... Hi, Simple formula to insert a SUM. Range("G22").Select Selection.End(xlDown).Select ActiveCell.Offset(2, 0).Select ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)" But it returns in Excel......=SUM('G5':G124) which doesn't work. Any idea? Thanks John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formual Issue
Can't see why i delected G22 either. Your formula worked spot on though.
Cheers Roger "Roger Govier" wrote: Hi John I can't see why you are selecting G22, but wanting the formula to run from G5. Try the following Sub test4() Dim lr As Long Range("G5").Select lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3).Select ActiveCell.Formula = "=Sum(" & "G5:G" & lr & ")" End Sub Amend to suit -- Regards Roger Govier "John" wrote in message ... Hi, Simple formula to insert a SUM. Range("G22").Select Selection.End(xlDown).Select ActiveCell.Offset(2, 0).Select ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)" But it returns in Excel......=SUM('G5':G124) which doesn't work. Any idea? Thanks John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formual Issue
Hi John
Can't see why i delected G22 either. Glad it just wasn't me!!! Thanks for the feedback, pleased you got it to work -- Regards Roger Govier "John" wrote in message ... Can't see why i delected G22 either. Your formula worked spot on though. Cheers Roger "Roger Govier" wrote: Hi John I can't see why you are selecting G22, but wanting the formula to run from G5. Try the following Sub test4() Dim lr As Long Range("G5").Select lr = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row Cells(Rows.Count, ActiveCell.Column).End(xlUp)(3).Select ActiveCell.Formula = "=Sum(" & "G5:G" & lr & ")" End Sub Amend to suit -- Regards Roger Govier "John" wrote in message ... Hi, Simple formula to insert a SUM. Range("G22").Select Selection.End(xlDown).Select ActiveCell.Offset(2, 0).Select ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)" But it returns in Excel......=SUM('G5':G124) which doesn't work. Any idea? Thanks John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formual Issue
with a macro you have to use the row/column method of identifying a
cell in a formula - when i record =sum(blah:blah), this is what you get in the VBA editor: ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-2]C)" (don't use this specific one - it doesn't sum as many rows as you wanted). susan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formual Issue
To the best of my knowledge you can't combine R1C1 and A1 reference
styles. Try This: Range("G22").End(xlDown).Offset(2).FormulaR1C1 = "=SUM(R5C:R[-2]C)" HTH Charles Chickering John wrote: Hi, Simple formula to insert a SUM. Range("G22").Select Selection.End(xlDown).Select ActiveCell.Offset(2, 0).Select ActiveCell.FormulaR1C1 = "=SUM(G5:R[-2]C)" But it returns in Excel......=SUM('G5':G124) which doesn't work. Any idea? Thanks John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formual issue | Excel Worksheet Functions | |||
8000 formual issue | Excel Discussion (Misc queries) | |||
Formual | Excel Worksheet Functions | |||
WHAT DOES FORMUAL =C8+ 15 DO | Excel Discussion (Misc queries) | |||
what is mean by ^ in formual | Excel Programming |