Insert formula using relative references with vba

[mim92]

Hi guys!! Just joined MrExcel so this is my 1st post/thread and I'm new to vba. I work in finance and have the following formula which I use to contra/net-off entries that cancel each other out i.e. debit 25 credit -25. I use this is various workbooks which helps me identify those values which actually make the total amount. Now I know simply retyping/copying the formula is easy to do but I wanted to know how I could determine certain properties in the formula as per below

Formula is = (COUNTIFS($B$2:B2,B2)<=MIN(COUNTIFS($B$2:$B$100000,-B2),(COUNTIFS($B$2:$B$100000,B2))))

1. Currently the above formula is in Column C so how do I determine what cell/column to look at when using vba to input the formula as each workbook will be different? i.e. I want the formula to look at column A instead of B (maybe input box?)

2. The no of records will always be different e.g. 500 rows instead of 100000 in formula above so I want the formula to be filled down according to the no of rows(maybe last row or cell?)

3. I want to select the range (Col/Cell) to input the formula e.g. C2 (input box again?)

4. If I want to insert my formula in col b but there is data I want to insert a column in front of col b

Think that's all for now

Thanks!

 

[Rijnsent]

Hi Mim,
welcome to the world of Excel . Most of what you want can be achieved with VBA/macros. A good way to start with macros is the macro recorder in Excel, combined with a small course (e.g. https://www.excel-pratique.com/en/vba.php or http://www.homeandlearn.org/ )
Back to your question: VBA can basically do anything you can do manually (and more). The main question is: how to you want to use that formula? As in: say you have 5 workbooks with data, all a bit different, but all having a column with values. You could run a macro to open those files one by one, search for the column with all values (or a fixed header?), check if the column next to it is empty and if so, put your formula there. After that I don't know what you want to do with those calculations? Are you looking through them by hand? Or do you fancy a list of every cell with a certain value?
Hope you got some ideas, that would help massively in helping you.
Cheers,
Koen