Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variable inside activeCell reference call
hey everybody
hoping someone can help with VBA syntax: I want to use the variable x as a row number reference, 'Insert Total calculation x = 0 Range("H1").Select 'go the end of the column Do While IsEmpty(ActiveCell.Offset(0, 0)) = False x = x + 1 ActiveCell.Offset(1, 0).Select Loop I have tried the two statements below but they seem to be illegal. ActiveCell.FormulaR1C1 "=SUM(R[ActiveCell.Offset(0,0)]C:R[ActiveCell.Offset(-&x,0)]C)" ActiveCell.FormulaR1C1 = "=SUM(R[-&x]C:R[-1]C)" Any help appreciated thank -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variable inside activeCell reference call
Is this what you want?
ActiveCell.FormulaR1C1 = _ "=SUM(R[-1]C:R" & ActiveCell.row - x & "C)" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "dabith " wrote in message ... hey everybody hoping someone can help with VBA syntax: I want to use the variable x as a row number reference, 'Insert Total calculation x = 0 Range("H1").Select 'go the end of the column Do While IsEmpty(ActiveCell.Offset(0, 0)) = False x = x + 1 ActiveCell.Offset(1, 0).Select Loop I have tried the two statements below but they seem to be illegal. ActiveCell.FormulaR1C1 = "=SUM(R[ActiveCell.Offset(0,0)]C:R[ActiveCell.Offset(-&x,0)]C)" ActiveCell.FormulaR1C1 = "=SUM(R[-&x]C:R[-1]C)" Any help appreciated thanks --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using variable inside activeCell reference call
Since you know you're starting in row 1, couldn't you just use that and the row
above: ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)" "dabith <" wrote: hey everybody hoping someone can help with VBA syntax: I want to use the variable x as a row number reference, 'Insert Total calculation x = 0 Range("H1").Select 'go the end of the column Do While IsEmpty(ActiveCell.Offset(0, 0)) = False x = x + 1 ActiveCell.Offset(1, 0).Select Loop I have tried the two statements below but they seem to be illegal. ActiveCell.FormulaR1C1 = "=SUM(R[ActiveCell.Offset(0,0)]C:R[ActiveCell.Offset(-&x,0)]C)" ActiveCell.FormulaR1C1 = "=SUM(R[-&x]C:R[-1]C)" Any help appreciated thanks --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
Can I use a variable to call up another sheet in Excel? | Excel Worksheet Functions | |||
Call a proc inside itself (HELP) | Excel Programming | |||
can I call a procedure using a variable | Excel Programming | |||
Using variable to call worksheets | Excel Programming |