Insert formula using relative references with vba

mim92

New Member
Joined
Sep 23, 2018
Messages
3
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top