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
- Select Columns by the Name of Column in the Header Row
- Copying and Pasting Values From One Column To Multiple Columns Using
- splitting textpane into two columns with a line between columns
- how to sort column by number of characters per cell ?
- Max number of sheets in workbook?
- I would like to fax directly from a fax number in a cell
- Delete everything in a workbook that is a number
- how to sum the even numbers only
- Addition of two numbers
- how to generate a rondom number btween two ranges
- Summing on varying number of rows in Excel using VBA
- Number crunching
- Convert Serial Number to a Date Function
- Detecting text vs numbers with an If/Then
- VBA to use unique password using volume serial number
- Count the number of rows on a worksheet, divide the count by 15
- code to select all sheets in a workbook and add 2 columns before Column A and B
- How to control number of textboxes according to user input
- Count unique names in listbox & put the number on the userform