Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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)

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.

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

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.

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.

Related Topics:

- Is it possible to have limited number of rows and columns in a sheet
- Change every second column to a negative number
- Getting the time for the Number of records retrived
- Getting CPU Number (Id) of a computer using Applet
- Converting a "Stringed" Number to a Number
- Converting a "Stringed" Number to a Number
- splitting textpane into two columns with a line between columns
- Select Columns by the Name of Column in the Header Row
- Copying and Pasting Values From One Column To Multiple Columns Using
- how to sort column by number of characters per cell ?
- Datagrid Display - right align numbers
- Math function to determine if a number is an int
- get the number of JList which is created by array
- changing the port number on JBOSS
- Jbuilder Serial number
- Number fields in Excel being considered NULL (empty)
- Error preallocating sequence numbers. The sequence table information
- How to access the Phone Number from the J2ME Application
- Formating the Number
- julian day number
- Pocket PC 2002 PDA to dail a phone number
- Number Format
- generating random numbers
- Fibonacci and Prime numbers
- convert from number (0-255) to character