Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Get Column by number

  Asked By: Steven    Date: Jan 28    Category: MS Office    Views: 921
  

I am trying to create dynamic formulas.
I need to know how can we dynamically incremant column !
eg:
=Sum(A1:A1000)
=Sum(B1:B1000)
=Sum(C1:C1000)
..
=Sum(Z1:Z1000)

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Leigh Carpenter     Answered On: Jan 28

This looks like a worksheet function, not VBA.
If so, it would be a simple matter of inserting the formula
in a cell, copying it HORIZONTALLY to the adjacent cells,
then MOVE the formula where you want it (if necessary).

If you are actually wanting to do this in VBA, then we can work
something out.

What is it you're trying to accomplish?

I think we need more specifics.

 
Answer #2    Answered By: William Evans     Answered On: Jan 28

Can I assume that your dynamically  incremented formulas  are actually going
into adjacent columns (not adjacent rows)? I.e. if you copy / paste the
formulas to the other cells, does Excel get it right?

If so, you can simply use copy / paste from within your VBA to do the same
thing.

Alternately, you can use the R1C1 version of the formula, using relative
addressing, and simply insert the same formula in each adjacent (column)
cell. The relative addressing will do the job.

If, instead, you are putting the formula into consecutive rows, then copy /
paste and relative R1C1 will not do the job.

You could use absolute R1C1 formulas. It's easier to increment column
numbers than column  letters.

Or you can write a simple function that gives you the column letter from a
column number. Such as

Option Explicit

Public Function ColumnLetter(ColumnNumber As Integer) As String
Const ToAlpha = 64
Dim FirstPart As Integer: FirstPart = Int((ColumnNumber - 1) / 26)
Dim SecondPart As Integer: SecondPart = ((ColumnNumber - 1) Mod 26) + 1
If FirstPart = 0 Then
ColumnLetter = Chr(ToAlpha + SecondPart)
Else
ColumnLetter = Chr(ToAlpha + FirstPart) + Chr(ToAlpha + SecondPart)
End If
End Function

 
Answer #3    Answered By: Audris Schmidt     Answered On: Jan 28

I tried to do this without VBA Code.

I suppose your data is in column  format like A1:A1000, B1:B2000, C1:C350 or
something like that.
I have written a formula which you can place in any cell


=SUM(INDIRECT(ADDRESS(1,ROWS($K$9:K9),TRUE)&":"&ADDRESS(COUNTA(INDIRECT(MID(ADDR\
ESS(1,ROWS($K$9:K9),TRUE),2,1)&":"&MID(ADDRESS(1,ROWS($K$9:K9),TRUE),2,1))),ROWS\
($K$9:K9))))

This is the formula which will add all values from Column A onwards. Just drag
the formula down to add the values in B column and so on.

A point to note. In the formula on top I assumed that the cell which I am
Writing this formula is Cell K9. If you are writing this formula in say cell J2
for eg. then change all the instances of K and 9 to J and 2 respectively.

 
Answer #4    Answered By: Rory Anderson     Answered On: Jan 28

Are you 'Selecting' a column  of interest to sum?
And does the column stop at Col Z?

I can implement the "OFFSET" function but need a bit more detail on your intent.
Something like this:

Sum(RangeOfCellsOfInterest)

RangeOfCellsOfInterest is replaced by the offset function that defines the
range of interest as an Array:
Offset(BaseCell,DeltaRow,DeltaColumn,RowsToInclude,ColumnsToInclude)

So The final expression is this:
Sum(offset(BaseCell,DeltaRow,DeltaColumn,RowsToInclude,ColumnsToInclude))

 
Didn't find what you were looking for? Find more on Get Column by number Or get search suggestion and latest updates.




Tagged: