Is it possible to create a VBA for the following

[Toonies]

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

 

[texasalynn]

so I not exactly clear what you want from VBA? If you want to put the formulas you have into a macro then use the macro recorder, select the cell with the formula and press F2 and enter. That will record the formula.

 

[Toonies]

Hi and thanks for the reply, what I would like if possible is for the macro to perform all the calculations. I have tried the Macro recorder but with no success as I am new to VBA.

Many thanks

Toonies

 

[texasalynn]

so what happens when you use the recorder that doesn't work?

 

[Toonies]

Thats right it does not work but then again am I doing it right to start with, I guess not.

 

[Toonies]

Can I ask in what order should I be recording the cells?

 

[texasalynn]

ok so let's start fresh

for your macro what do you expect as the results? The formulas a single cell answer? A song and a dance? LOL (just kidding)

It's difficult to understand when you say it doesn't work without some specifics. Remember we are not there to see what you are seeing. Also if you want to post your code here would be helpful as well.

 

[Toonies]

Ok heres a copy of the recorded macro

Sub Test1()
'
' Test1 Macro
' Macro recorded 03/05/2011 by Tom
'
'
Range("F9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNT(RC3,RC4)=2,MAX(0,MIN(R4C,RC4+(RC3>RC4))-MAX(R3C,RC3)-RC[-1]))*R7C*IF('Not in Public Holiday'!RC[-3]=""BANK ""&LOOKUP(""zzz"",R2C),1,1)*24"
Range("G9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNT(RC3,RC[-3])=2,MAX(0,MIN(R4C,RC[-3]+(RC3>RC[-3]))-MAX(R3C,RC3)-'Personnel Rota'!RC6))*R7C7*IF('Not in Public Holiday'!RC[-4]=""BANK ""&LOOKUP(""zzz"",R2C[-1]),1,1)*24"
Range("H9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNT(RC3,RC[-4])=2,MAX(0,MIN(R4C,RC[-4]+(RC3>RC[-4]))-MAX(R3C,RC3)))*R7C*IF('Not in Public Holiday'!RC[-5]=""BANK ""&LOOKUP(""zzz"",R2C[-2]),1,1)*24"
Range("I9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNT(RC3,RC[-5])=2,MAX(0,MIN(R4C,RC[-5]+(RC3>RC[-5]))-MAX(R3C,RC3)-RC[4]))*R7C*IF('Not in Public Holiday'!RC[-4]=""BANK ""&LOOKUP(""zzz"",R2C[9]),1,1)*24"
Range("J9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNT(RC3,RC[-6])=2,MAX(0,MIN(R4C,RC[-6]+(RC3>RC[-6]))-MAX(R3C,RC3)))*R7C*IF('Not in Public Holiday'!RC[-5]=""BANK ""&LOOKUP(""zzz"",R2C[8]),1,1)*24"
Range("K9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNT(RC3,RC[-7])=2,MAX(0,MIN(R4C,RC[-7]+(RC3>RC[-7]))-MAX(R3C,RC3)))*R7C*IF('Not in Public Holiday'!RC[-6]=""BANK ""&LOOKUP(""zzz"",R2C[7]),1,1)*24"
Range("L9").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-6]:RC[-1])"
Range("M9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNT(RC3,RC4)=2,MAX(0,MIN(R7C[-1],RC4+(RC3>RC4))-MAX(R6C[-1],RC3)),"""")"
Range("E9").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNT(RC3,RC4)=2,MAX(0,MIN(R4C[7],RC4+(RC3>RC4))-MAX(R3C[7],RC3)),"""")"
Range("C4").Select
ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(PublicHolidayList,R[2]C)>0,INDEX(PublicHolidaysTable,MATCH(R[2]C,PublicHolidayDate,0),2),"""")"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=SUM('Personnel Rota'!R[104]C[-2]*1.3333)"
Range("G7").Select
ActiveCell.FormulaR1C1 = "=SUM('Personnel Rota'!R[104]C[-3])"
Range("H7").Select
ActiveCell.FormulaR1C1 = "=SUM('Personnel Rota'!R[104]C[-4]*1.3333)"
Range("I7").Select
ActiveCell.FormulaR1C1 = "=SUM('Personnel Rota'!R[104]C[-5]*1.333)"
Range("J7").Select
ActiveCell.FormulaR1C1 = "=SUM('Personnel Rota'!R[104]C[-6])"
Range("K7").Select
ActiveCell.FormulaR1C1 = "=SUM('Personnel Rota'!R[104]C[-7]*1.333)"
Range("K8").Select
End Sub

this is in the modular

 

[texasalynn]

ok so you did get the formulas in your macro. What part is not working?

 

[Toonies]

WOW Ok when I run the macro it works
is ther anyway that I can automate it?

Do you think that change event might work although it may have do be dependant
on the cells 'Personnel Rota'!C9 or 'Personnel Rota'!D9