MS Excel- Replacing Formulas with Code

Hi Folks
I have a large Excel Workbook that has many Sheets containing Formulas in many Columns.
For example:
Sheet Workouts! contains the following formula in Col D and runs from row A3 to row A29,263 inclusive:

=COUNTIFS(Data!$I$2:$I$40000,D$1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($A3),Data!$P$1:$BT$1,0)),1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($B3),Data!$P$1:$BT$1,0)),1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($C3),Data!$P$1:$BT$1,0)),1)

If I can see the Code for the above example I think I would be capable of adjusting same to replace all the other formulas.
I have not attached an actual example due to the complexity of setting up a small sample Workbook. However if it is absolutely necessary
then I will take a shot at it.

Thanks
Phil
PWM16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
If I can see the Code for the above example I think I would be capable of adjusting same to replace all the other formulas
You should try the macro recorder. Formulas to be copied are done in R1C1 format and for this the macro recorder is almost inevitable.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may simple place this formula in col. D like this....
Range("D3:D29263").Formula = "=COUNTIFS(Data!$I$2:$I$40000,D$1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($A3),Data!$P$1:$BT$1,0)),1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($B3),Data!$P$1:$BT$1,0)),1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($C3),Data!$P$1:$BT$1,0)),1)"

Open in new window

PWM16Author Commented:
Hi
@sktneer , not sure where/how that is placed in Col D ?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Press Alt+F11 to open VBA Editor --> On VBA Editor --> Insert --> Module --> And paste the following code in the opened code window.

Click inside the code somewhere and press F5 to run the code.
Sub InsertFormula()

'Select the sheet where you want to place this formula
'In this case the code assumes that you will place this formula on Sheet1
'If it is not Sheet1, change it in the line below
Sheets("Sheet1").Select

'The following line will place the formula in the range D3:D29263
Range("D3:D29263").Formula = "=COUNTIFS(Data!$I$2:$I$40000,D$1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($A3),Data!$P$1:$BT$1,0)),1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($B3),Data!$P$1:$BT$1,0)),1,INDEX(Data!$P$2:$BT$40000,,MATCH(TRIM($C3),Data!$P$1:$BT$1,0)),1)"
                                          
End Sub

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.