using Excel index notation for Sum formula

[heathlovell]

Hello,
I have been using Worksheet.cells(1,2).value in Excel to cycle through data using a For statement. This works great other than there are certain cells that I want to put a Sum (=Sum(a1:a4)) statement in. I can't figure out a way to do this using just the index values for the row and column.
Also, how do I let the user run the macro without having to have a certain spreadsheet open?
Thanks for the help,
Heath

 

[Nikita6003]

Hiya,

Trw using

Sum(Worksheet.Range(Worksheet.Cells(1,1), Worksheet.Cells(4,1)))

for Sum("$A$1:$A$4&quot
PLZ NOTE: you'll get absolute cell addresses in the resulting formula (just in case you want to be copying down


HTH

Cheers
Nikki

 

[RobBroekhuis]

If you're trying to put a formula into a cell which you're referencing by row and column number, here's two approaches that may be useful:

1. Use the formulaR1C1 property of the cell for relative referencing, e.g.
cells(r,c).formulaR1C1="=sum(R[-4]C:R[-1]C)"
to sum the four cells above the current cell.

2. Use the cells(r,c).address property to construct the formula:

cells(r,c).formula="=sum(" & cells(r-4,c).address & ":" & cells(r-1,c).address & &quot"

I hope that helps.
Rob

 

[Zathras]

FYI, there are parameters you can use with the Address property, here's a quick demo to illustrate a couple (to eliminate the dollar signs):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  MsgBox Target.Address(False, False)
End Sub

Here is an extract from the help file:

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

 

[RobBroekhuis]

Good add, Zathras. If "false" and "true" makes your VBA expressions too unwieldy, you can also use 0 and 1, e.g.
activecell.address(0,0) will yield ("A1&quot
Rob

 

[heathlovell]

Thanks for the help. That is exactly what I needed. Any advice on how to make the macro available on demand?
Thanks,
Heath

 

[RobBroekhuis]

How would you like to make it available? As a button on your worksheet? On a toolbar? From the top menu? From a keystroke combination? All of the above are possible.
Rob

 

[heathlovell]

I think I would like it to be either on the toolbar or a pulldown menu. Can you walk me through the steps to do this?

Thanks,
Heath

 

[RobBroekhuis]

Take a look at this, and see if it comes close to what you want:

Dim ToolsMenu As CommandBarPopup
Dim MyItem As CommandBarControl
Set ToolsMenu = Application.CommandBars(1).Controls("Tools&quot
Set MyItem = ToolsMenu.Controls.Add
MyItem.Caption = "My Macro"
MyItem.OnAction = "MyMacro"
MyItem.BeginGroup = True

This adds an additional item to the bottom of the tools menu item on the default menu bar, to run the "MyMacro" macro. Using similar code, you can create a new pulldown menu for the default menu bar, add buttons to existing toolbars, or create new toolbars with your own buttons. Once you decide which way you'd like to go and you've played around a bit, we'll be able to provide more specific help
Rob

 

[heathlovell]

That will work great. The only problem I have is where to put the macro and the listed code. When I first created the macro, Excel put it in the module for the spreadsheet I was working on. For me to use the macro, I have to have the spreadsheet open. Where do I put the code you listed so that the macro will be available anytime the user opens Excel. Is it possible to make it a module that you can add-in from the add-in menu anytime you need it?
Thanks for the help,
Heath

 

[RobBroekhuis]

The best place to put general-purpose macros that need to be available at all times is in personal.xls. This file is opened by Excel when it opens, and normally remains hidden. If you don't have a personal.xls already, the easiest way to create one (since it needs to go in the correct folder for Excel to recognize it) is to use the macro recorder to record a new macro, and choose "in personal.xls" from the dropdown menu asking where to put the macro.
If you are distributing the macro to many other users, personal.xls probably isn't such a good choice (since it is really meant to be "personal&quot - in that case, you might consider using an add-in instead.
Rob