More information - see prior post

[JEHalm]

Sample data


Type Number Code City State Bldg Amount

N N1234567 P052534 Chicago IL IL0303 $500,000

R R7654321 P057499 Ottawa OH OH1298 $3,500.00

[counta] [sum]


Variable columns lengths each day. One day 15,333...next 15,789 etc.

 

[Hui]

JEHalm


This is a great case where Dynamic Ranges or Named Formulas can be used.

Setup a Named Formula

Formula, Name Manager, New

Name: Type

Refers to: =OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1)


add a second Named Formula

Name: Amount

Refers To: =OFFSET(Sheet1!$G$1,1,,COUNTA(Sheet1!$G:$G)-1)


You can now put 2 formulas where ever you want, but not in Column A or G


Count =Counta(Type)

Amount: =Sum(Amount)


Adjust page name and ranges to suit

 

[JEHalm]

Hui, thanks so much for the help regarding this request. Not just for the suggestions and formula, it made me think and was a valuable lesson. VBA is so extensive I feel lost at times. Days like this makes it worth it. Thanks again. I assume the next step would be to make a small macro to run the formula's??

 

[Hui]

You don't need macro's for this ?

Make sure calculation is set to Automatic (Formula Bar, Calculation Options Tab)

 

[JEHalm]

Hi Hui,is there any way to use the named formula [counta(type)] and [sum(amount)] in conjunction with the code below? I like the way the named formulas work. repeatability


Private Sub CommandButton1_Click()

Range("h1").Select

Range("h65536").End(xlUp).Select

ActiveCell.Offset(1, 0).Select

ActiveCell = Sum(amount)


'Range(Selection, Selection.End(xlDown)).Select

'Selection.Offset(1, 0).Select


'ActiveCell.Offset(1, 0).Select


End Sub