Convert excel formula to vba

KX13ZN

New Member
Joined
Sep 12, 2019
Messages
34
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
I have a formula that checks 7 cells in a row to see if they have a 1 in them. It then counts how many days have passed from a set date using those 1's to correspond to specific days.

=IF(P6=0,0,IF(D6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111110"),0)+IF(E6=1,NETWORKDAYS.INTL(P6,TODAY(),"0111111"),0)+IF(F6=1,NETWORKDAYS.INTL(P6,TODAY(),"1011111"),0)+IF(G6=1,NETWORKDAYS.INTL(P6,TODAY(),"1101111"),0)+IF(H6=1,NETWORKDAYS.INTL(P6,TODAY(),"1110111"),0)+IF(I6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111011"),0)+IF(J6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111101"),0))

D6E6F6G6H6I6J6
SunMonTueWedThuFriSat

And Cell P6 contains the date.

The formula is set up like this because its based off of shifts which are not just mon-fri or sat-sun, they vary depending on the employee.

I need to convert that into vba so that I can add a new row with another macro and copy this equation in, doing it so the row number changes to the current row.

Is there way to set it so the equation auto updates or does it have to be completely re-written and if so how?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
One way ..

1. create VBA constant F using the current formula string BUT doubling up EVERY quote marks
this formula...
=IF(P6=0,0,IF(D6=1,NETWORKDAYS.INTL(P6,TODAY(),"1111110"),0)+IF(E6=1,NETWORKDAYS.INTL(P6,TODAY(),"0111111"),0)+ .... etc
becomes ...
Const F = "=IF(P6=0,0,IF(D6=1,NETWORKDAYS.INTL(P6,TODAY(),""1111110""),0)+IF(E6=1,NETWORKDAYS.INTL(P6,TODAY(),""0111111""),0)+ .... etc "

2. replace the 6 in F with row number in this line ...
Replace(F, 6, r)

Like this:
VBA Code:
    Const F = "=IF(P6=0,0,IF(D6=1,NETWORKDAYS.INTL(P6,TODAY(),""1111110""),0)+IF(E6=1,NETWORKDAYS.INTL(P6,TODAY(),""0111111""),0)+IF(F6=1,NETWORKDAYS.INTL(P6,TODAY(),""1011111""),0)+IF(G6=1,NETWORKDAYS.INTL(P6,TODAY(),""1101111""),0)+IF(H6=1,NETWORKDAYS.INTL(P6,TODAY(),""1110111""),0)+IF(I6=1,NETWORKDAYS.INTL(P6,TODAY(),""1111011""),0)+IF(J6=1,NETWORKDAYS.INTL(P6,TODAY(),""1111101""),0))"
    Dim r As Long, cel As Range
    Set cel = Range("T20")                      'used for this example
    r = cel.Row

    cel.Formula = Replace(F, 6, r)



The above example returned

=IF(P20=0,0,IF(D20=1,NETWORKDAYS.INTL(P20,TODAY(),"1111110"),0)+IF(E20=1,NETWORKDAYS.INTL(P20,TODAY(),"0111111"),0)+IF(F20=1,NETWORKDAYS.INTL(P20,TODAY(),"1011111"),0)+IF(G20=1,NETWORKDAYS.INTL(P20,TODAY(),"1101111"),0)+IF(H20=1,NETWORKDAYS.INTL(P20,TODAY(),"1110111"),0)+IF(I20=1,NETWORKDAYS.INTL(P20,TODAY(),"1111011"),0)+IF(J20=1,NETWORKDAYS.INTL(P20,TODAY(),"1111101"),0))
 
Upvote 0
Thank you, i'll go with that. I even feel confident i can edit that to suit other needs, thanx
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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