Is it possible to create a VBA for the following

T

Toonies

Board Regular
Joined
Jun 8, 2009
Messages
236
Hi all and thank you for taking the time to look at my question, which is as follows.

I am using the following formulas to calculate differential pay which works ok.

I am wondering can it be converted into VBA format?

When it comes to VBA I am just starting to pick up the basics and I mean the basics.

ok here is a snippet of my spreadsheet.

OS Windows Vista, Excel 2003

Pay

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 6px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 6px"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 6px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt" colSpan=3>HOLIDAY</TD><TD style="FONT-SIZE: 14pt" colSpan=3></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD></TR><TR style="HEIGHT: 39px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Shift Start</TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">0:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">6:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">22:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">24:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">30:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">46:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">3:00</TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD></TR><TR style="HEIGHT: 40px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="BACKGROUND-COLOR: #ff0000; FONT-SIZE: 14pt">BANK HOLIDAY</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Shift End</TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">6:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">22:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">24:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">30:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">46:00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">48:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">3:30</TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD></TR><TR style="HEIGHT: 6px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD></TR><TR style="HEIGHT: 40px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt; FONT-WEIGHT: bold" colSpan=2>Fri 22 Apr 2011</TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt">Break</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">0:30</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">0:30</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">27:00</TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD></TR><TR style="HEIGHT: 40px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Shift Start</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt; FONT-WEIGHT: bold">Shift End</TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt">Pay Rate</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">£8.48</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">£6.36</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">£8.48</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">£8.48</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">£6.36</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">£8.48</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">27:30</TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD></TR><TR style="HEIGHT: 6px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt; FONT-WEIGHT: bold"></TD><TD style="FONT-SIZE: 14pt; FONT-WEIGHT: bold"></TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="FONT-SIZE: 14pt"></TD><TD style="COLOR: #ffffff; FONT-SIZE: 14pt"></TD></TR><TR style="HEIGHT: 52px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 14pt"></TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">22:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 14pt">6:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ffffff; FONT-SIZE: 14pt">00:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ffffff; FONT-SIZE: 14pt">£0.00</TD><TD style="TEXT-ALIGN: right; COLOR: #ffffff; FONT-SIZE: 14pt">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">£16.96</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">£46.63</TD><TD style="TEXT-ALIGN: right; COLOR: #ffffff; FONT-SIZE: 14pt">£0.00</TD><TD style="TEXT-ALIGN: right; COLOR: #ffffff; FONT-SIZE: 14pt">£0.00</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 14pt">£63.59</TD><TD style="TEXT-ALIGN: right; COLOR: #ffffff; FONT-SIZE: 14pt">00:30</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C4</TD><TD>=IF(COUNTIF(PublicHolidayList,C6)>0,INDEX(PublicHolidaysTable,MATCH(C6,PublicHolidayDate,0),2),"")</TD></TR><TR><TD>C6</TD><TD>=SUM('Personnel Rota'!C6)</TD></TR><TR><TD>F6</TD><TD>=IF(C9="",0,(C9>D9)*MEDIAN(0,D9-L$3,L$4-L$3)+MAX(0,MIN(L$4,D9+(C9>D9))-MAX(L$3,C9)))</TD></TR><TR><TD>I6</TD><TD>=IF(C9="",0,(C9>D9)*MEDIAN(0,D9-L$6,L$7-L$6)+MAX(0,MIN(L$7,D9+(C9>D9))-MAX(L$6,C9)))</TD></TR><TR><TD>F7</TD><TD>=SUM('Personnel Rota'!D111*1.3333)</TD></TR><TR><TD>G7</TD><TD>=SUM('Personnel Rota'!D111)</TD></TR><TR><TD>H7</TD><TD>=SUM('Personnel Rota'!D111*1.3333)</TD></TR><TR><TD>I7</TD><TD>=SUM('Personnel Rota'!D111*1.333)</TD></TR><TR><TD>J7</TD><TD>=SUM('Personnel Rota'!D111)</TD></TR><TR><TD>K7</TD><TD>=SUM('Personnel Rota'!D111*1.333)</TD></TR><TR><TD>C9</TD><TD>=IF('Personnel Rota'!C9="","",'Personnel Rota'!C9)</TD></TR><TR><TD>D9</TD><TD>=IF('Personnel Rota'!D9="","",'Personnel Rota'!D9)</TD></TR><TR><TD>E9</TD><TD>=IF(COUNT($C9,$D9)=2,MAX(0,MIN(L$4,$D9+($C9>$D9))-MAX(L$3,$C9)),"")</TD></TR><TR><TD>F9</TD><TD>=IF(COUNT($C9,$D9)=2,MAX(0,MIN(F$4,$D9+($C9>$D9))-MAX(F$3,$C9)-E9))*F$7*IF('Not in Public Holiday'!C9="BANK "&LOOKUP("zzz",F$2),1,1)*24</TD></TR><TR><TD>G9</TD><TD>=IF(COUNT($C9,D9)=2,MAX(0,MIN(G$4,D9+($C9>D9))-MAX(G$3,$C9)-'Personnel Rota'!$F9))*$G$7*IF('Not in Public Holiday'!C9="BANK "&LOOKUP("zzz",F$2),1,1)*24</TD></TR><TR><TD>H9</TD><TD>=IF(COUNT($C9,D9)=2,MAX(0,MIN(H$4,D9+($C9>D9))-MAX(H$3,$C9)))*H$7*IF('Not in Public Holiday'!C9="BANK "&LOOKUP("zzz",F$2),1,1)*24</TD></TR><TR><TD>I9</TD><TD>=IF(COUNT($C9,D9)=2,MAX(0,MIN(I$4,D9+($C9>D9))-MAX(I$3,$C9)-M9))*I$7*IF('Not in Public Holiday'!E9="BANK "&LOOKUP("zzz",R$2),1,1)*24</TD></TR><TR><TD>J9</TD><TD>=IF(COUNT($C9,D9)=2,MAX(0,MIN(J$4,D9+($C9>D9))-MAX(J$3,$C9)))*J$7*IF('Not in Public Holiday'!E9="BANK "&LOOKUP("zzz",R$2),1,1)*24</TD></TR><TR><TD>K9</TD><TD>=IF(COUNT($C9,D9)=2,MAX(0,MIN(K$4,D9+($C9>D9))-MAX(K$3,$C9)))*K$7*IF('Not in Public Holiday'!E9="BANK "&LOOKUP("zzz",R$2),1,1)*24</TD></TR><TR><TD>L9</TD><TD>=SUM(F9:K9)</TD></TR><TR><TD>M9</TD><TD>=IF(COUNT($C9,$D9)=2,MAX(0,MIN(L$7,$D9+($C9>$D9))-MAX(L$6,$C9)),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4


I look forward to any replies

Many thanks

Toonies
 
Last edited:
Sort by date Sort by votes
automate meaing what? when you open the file? when you type in something? and if so what would that something be?
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
S
  • Solved
Calculate annual sales per country
Replies
5
Views
159
AhoyNC
A
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M

Forum statistics

Threads
1,224,613
Messages
6,179,903
Members
452,948
Latest member
Dupuhini

Share this page

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