Help changing counta/countifs formulas into macros

[tatendatiffany]

Hi

Could someone please help me i have created a couple of countif and counta formula but i want to turn them into macros. Please any guidance would be appreciated

=SUM(OFFSET(KTPT81T!G5,0,0,COUNT(KTPT81T!G5:G1000)))

=COUNTIF(TABF124!$G:K,"Y")
=COUNTA(KTPT80T!$G:$G)-2

how can i turn these into macros, i am still a beginner and am struggling to understand macros.

Thank you.

 

[jim may]

1) Select or highlight your formula in the spreadsheet
2) Start the Record Macro feature
3) In the spreadsheet window (with your formula as the activecell) press the F2 key,
which puts your cell into Edit Mode.
4) Quickly press the enter key again to leave Edit mode.
5) Stop the Macro Recorder.
6) Switch to the VBE and view the code created by the Macro.

HTH,

Jim

 

[jasonb75]

=SUM(OFFSET(KTPT81T!G5,0,0,COUNT(KTPT81T!G5:G1000)))

Seems like a bit of overkill with risk of error when

=SUM(G5:G1000)

should suffice.

 

[tatendatiffany]

Thank you both for your responses but I forgot to mention the reason why i wanted to use a macros instead of formulas because I have multiple formulas as i am looking up 28 worksheets and the ranges are dynamic and i need to use macros because my excel is running out of resources.

Also I have recorded the macro so now do i delete the formula in the excel sheet and run the macro instead (sorry for the dumb question)

 

[jasonb75]

In your situation the macro would seem a false economy, if you delete the formula and run the macro it just puts the formula back.

Do you really need dynamic ranges? As I pointed out with your example formula, the dynamic aspect is an overkill that serves only the purpose of creating errors.

Making your formula more efficient will probably be a more effictive method of freeing up resources.

 

[tatendatiffany]

yes the formula needs to be dynamic because I am going to continually add data to the tables. or should i change it to maybe read thousands of lines but not make it dynamic??
also if i do that can i can change it to a macro easily right.


Thank you

 

[jasonb75]

The macro for your purpose is a false economy, as soon as you run it you will be adding the formula straight back in using as much resourse as before to calculate them, and additional resource to run the macro.

In addition to this,

=SUM(OFFSET(KTPT81T!G5,0,0,COUNT(KTPT81T!G5:G1000))) is volatile

=SUM(G5:G1000) is non-volatile.

See the section on Volatile functions at http://msdn.microsoft.com/en-us/library/bb687891(v=office.12).aspx

 

[tatendatiffany]

ok thank you i will use your approach but i still want to do it as a macros not a singular formulas.


Thank you

 

[tatendatiffany]

can i ask another question please, why could i be getting this error

"Sub count()
With ThisWorkbook.Sheets("Sheet126").range("G5:G1000")
With range(.Cells(G, 1), .Cells(.Rows.count, 1).End(xlUp))
.Parent.range("B6").FormulaR1C1 = "=COUNTA(" & .Address(, , xlR1C1) & ")"
.Parent.range("B7").FormulaR1C1 = "=COUNTIF(" & .Address(, , xlR1C1) & ", ""N"")"
End With
End With
End Sub"

on the line highlighted in bold?? i double checked but i cant seem to find anything wrong

 

[jasonb75]

Are you sure you have a sheet called "Sheet126"?

That's the only thing I can see that could cause an error.