asked on

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

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

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

PWM16

ASKER

Hi
@sktneer , not sure where/how that is placed in Col D ?

Subodh Tiwari (Neeraj)

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