Dynamic Table Cell References

[HiAspire]

I have written a few macros in Office 2000 but I've gotten stuck on a macro I'm using to generate invoices.

I need it to do some basic mathmatics for me from a dynamic table that can be various rows long depending on user clicks. Easy enough with an established set table referencing known cells using commands like cell A2 times cell B2, or = sum(Table1 c:c).

QTY PRICE TOTAL
2 1.50 3.00
4 2.00 8.00
11.00

But what about when you don't know how many rows are going to be in a dynamic table that's created one row at a time (including the right total collumn) by user clicks and not fixed where you can say cell a2 but instead need to say the current value of cell A at whatever the current row is, like a4 or a5 or a6 depending.

This is an oversimplification because there are many more collumns involved. Can you determine the current row and use that in the total formula?

Or can you say something like "multiply the cell 4 collumns to the left, by the cell 2 collumns to the left"? So that you don't have to worry about the row number at all? I know you can do some things like that with other commands like sum(LEFT). Can I create a variable to keep track of how many rows are being used and somehow incorporate that number into the cell reference (if that variable equals 4, then use A4)? What is the best way to do this?

Hopefully I'm over-thinking it and missing something simple. Thank for your help.

 

[dwilson01]

You have asked several questions,let me try to answer then in order.
Finding the row number is easy, try

ActiveCell.Row

Formulas using columns to the right, try

iRow = ActiveCell.Row
iCol = ActiveCell.Column
YourCell = Cells(iRow,iCol + 2) * Cells(iRow,iCol + 4)

Using this information can be tricky because the data is being entered row by row and column by column. This could cause errors if the formula uses a blank cell. There are ways around this. I can email you a sample if you would like. Post your email address and I will send you a simplified sample.

 

[dwilson01]

Okay, I couldn't wait, so here is some test code that works on a sheet that contains Qty in Column 1, Price in Column 2 and Total in Column 3. Notice that if you add values in columns 1 & 2 the Total is calculated. If you change an existing value, the Total is updated. Hope this gets you started toward a solution. Add this to the worksheet containing the data in the change event.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 1 Or Target.Column = 2 Then
  iRow = Target.Row
  If Cells(iRow, 1) <> &quot;&quot; And Cells(iRow, 2) <> &quot;&quot; Then
    Cells(iRow, 3) = Cells(iRow, 1) * Cells(iRow, 2)
    ActiveSheet.Range(Cells(iRow + 1, 3).Address) _
      .Formula = &quot;=SUM(C1:C&quot; & iRow & &quot;)&quot;
  End If
End If

End Sub

dwilson01

 

[oharab]

Another thing to look at would be the =INDIRECT worksheet function. THis lets you build a range string in one cell, then refer to it as if it were a range. This is hard to explain, so an example:

In cell D1 type ="A1:A"&count(A:A)

if there are 5 rows in column A, D1 will show "A1:A5" (Without the quote marks)
If you then want to sum those rows in column A then you can use =sum(Indirect(D1))

You can play with it and the =CELL function to do what you want.

HTH

Ben
----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[HiAspire]

Actually this is a macro for Word, not Excel. I guess I didn't make that clear. It's a Word table instead of a worksheet. As I have it working now, clicking on an item (macrobutton) prints out the item description and some other text depending upon the item and the price (within a Word table). The length of that table and the number of rows is dependent upon how many items you click on.

 

[verplexd]

To find the current cell coordinates in a table in Word use this:

cellRow = Selection.Cells(1).RowIndex
cellCol = Selection.Cells(1).ColumnIndex

Is that what you are talking about?