Formula in VB where number of Columns are unknown

[Carly]

We are trying to use a CountA formula on a spreadsheet where the amount of Columns change each time the report is run. We have had this before where we wanted the formula to be at the end of each column and used the following formula:

With [A65536].End(xlUp).Offset(1).Resize(, [IV1].End(xlToLeft).Column)
.Formula = "SUM(R2C:R[-1]C)!
.Value = .Value
End With

This then created a Sum formula against each column.

but this time we would like the formula to be at the end of each row (no. of rows will change also), so we revamped the formula to the following:

With [IV1].End(xlToLeft).Offset(1, 1).Resize(, [A65536].End(xlUp).Column)
.Formula = "=COUNTA(RC6:RC[-1])"
.Value = .Value
End With

This puts the formula in the cell where we want it to start but it doesn't go all the way down.

Is there anyway that this can be done?

Please help

Carly

 

[Juan Pablo González]

This:

[A65536].End(xlUp).Column

should always return 1, because the column of A is always 1. So, try changing the .Column to .Row on that statement.

 

[Carly]

We tried this and it gave the following error:

Run Time Error 1004:

Application-defined or object-defined error

 

[Juan Pablo González]

The Resize method works exactly as selecting using the mouse or the keyboard in Excel.

If you resize a range in Excel you should see something like

10Rx5C

that means 10 rows by 5 columns.

The .Resize method expects the same

.Resize(NumberOfRows, NumberofColumns)

and, you're giving, in your code, the current number of rows

<pre>.Resize(,</pre>

with the "found" number of columns in [A65536].End(xlUp)

<pre> [A65536].End(xlUp).Column) </pre>
So, if you "switch" the order, to the "right" order, that is, give the found rows in column A, and the current number of columns (Which I think is 1 in this case), you should have

<pre>.Resize([A65536].End(xlUp).Row,1)</pre>

 

[Carly]

Cheers that's worked fine!!!!!