Posts by Yogi Anand

Yogi Anand · Jul 14th 2003

Hi spnz:

Try ...

=if(isblank(f52),0,VLOOKUP(F52,LookupTable,2,0))

Yogi Anand · Apr 21st 2003

Hi tazmanian:

I suggest you use the following DATA|Validation schemes ...

for cells C3:D20 ...
=AND(OR(C3=0,C3=1),COUNTA($C3:$D3)=1)

and for cells E3:I20 ...
=AND(OR(E3=0,E3=1),COUNTA($E3:$I3)=1)

Please post back if it helps -- I have also uploaded the worksheet assessment2_a.xls for your reference.

Yogi Anand · Apr 21st 2003

Not so at all Richie!

I follow your posts with great interest -- Keep EXCELing!

Yogi Anand · Apr 21st 2003

Hi perthTam:

Welcome to the Board!

Try ...

activecell.rows(rows.count-activecell.Row+1).end(xlUp).select -- toselect the nonblank cell, and

activecell.resize(activecell.rows(rows.count-activecell.Row+1).end(xlUp).row-activecell.Row+1).select
-- to select the range

Yogi Anand · Apr 21st 2003

Hi PNeely:

Welcome to the Board!

If I have undestood you correctly that you want a macro to start executing when you select a cell in a worksheet -- then try ...

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
   yRunMe
End Sub

The code is for a Worksheet_SelectionChange event and runs the macro called yRunMe on selecting any cell in the worksheet for which the code is installed.

Yogi Anand · Apr 21st 2003

Hi Sekhar:

Here is the VBA code ...

Code

Sub yAddSubtractTme()
    For Each cell In [B:B]
        If cell.Row < 2 Then GoTo Continue1
        If cell = "" Then Exit For
            cell.Offset(0, 1) = cell - cell.Offset(0, -1)
            If cell.Offset(0, 1) - [sheet9a!$C$1] &gt; 0 Then cell(1, 3) = WorksheetFunction.Text(Abs((cell.Offset(0, 1) - [sheet9a!$C$1])), "h:mm:ss") & " over"
            If cell.Offset(0, 1) - [sheet9a!$C$1] < 0 Then cell(1, 3) = WorksheetFunction.Text(Abs((cell.Offset(0, 1) - [sheet9a!$C$1])), "h:mm:ss") & " under"
Continue1:
    Next cell
End Sub

I applied the code to the source data shown in aqua, and the macro posted the results in the cells C2:D4 as shown in the following simulation ...

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - y030420h1.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl97 : OS = Windows 98 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp <A onclick=show_popup(); href="javascript:void(0)">(<U>A</U>)bout</A></TD><TD vAlign=center align=right><FORM name=formCb821292><INPUT onclick='window.clipboardData.setData("Text",document.formFb276773.sltNb325610.value);' type=button value="Copy Formula" name=btCb878235></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb276773><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb276773.txbFb686414.value = document.formFb276773.sltNb325610.value" name=sltNb325610><OPTION value="0:15:00 over" selected>A1</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value=StartTime name=txbFb686414></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">StartTime</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">EndTime</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; BORDER-TOP: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">7:15:00</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Over_Under</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">7:59:00</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">16:47:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">8:48:00</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1:33:00 over</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">8:00:00</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">15:00:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">7:00:00</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">0:15:00 under</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">8:00:00</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: center">15:30:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">7:30:00</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">0:15:00 over</TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet9a</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker 2.20] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

Yogi Anand · Apr 21st 2003

Hi Sekhar:

If I have understood you correctly, you should find the following simulation of interest ...

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - y030419h1.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl97 : OS = Windows 98 </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp <A onclick=show_popup(); href="javascript:void(0)">(<U>A</U>)bout</A></TD><TD vAlign=center align=right><FORM name=formCb404644><INPUT onclick='window.clipboardData.setData("Text",document.formFb995560.sltNb570403.value);' type=button value="Copy Formula" name=btCb120145></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb995560><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb995560.txbFb655397.value = document.formFb995560.sltNb570403.value" name=sltNb570403><OPTION value==B2-A2 selected>C2<OPTION value='=TEXT(ABS(C2-C$1),"h:mm:ss")&IF((C2-C$1)<0," under",IF((C2-C$1)=0,""," over"))'>D2<OPTION value==B3-A3>C3<OPTION value='=TEXT(ABS(C3-C$1),"h:mm:ss")&IF((C3-C$1)<0," under",IF((C3-C$1)=0,""," over"))'>D3<OPTION value==B4-A4>C4<OPTION value='=TEXT(ABS(C4-C$1),"h:mm:ss")&IF((C4-C$1)<0," under",IF((C4-C$1)=0,""," over"))'>D4</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value==B2-A2 name=txbFb655397></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">StartTime</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">EndTime</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; BORDER-TOP: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: center">7:15:00</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">Over_Under</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">7:59:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">16:47:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"><A onclick="document.formFb995560.sltNb570403.options[0].selected=true; document.formFb995560.txbFb655397.value = document.formFb995560.sltNb570403.value;" href="javascript:void(0);">8:48:00</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"><A onclick="document.formFb995560.sltNb570403.options[1].selected=true; document.formFb995560.txbFb655397.value = document.formFb995560.sltNb570403.value;" href="javascript:void(0);">1:33:00 over</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">8:00:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">15:00:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"><A onclick="document.formFb995560.sltNb570403.options[2].selected=true; document.formFb995560.txbFb655397.value = document.formFb995560.sltNb570403.value;" href="javascript:void(0);">7:00:00</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"><A onclick="document.formFb995560.sltNb570403.options[3].selected=true; document.formFb995560.txbFb655397.value = document.formFb995560.sltNb570403.value;" href="javascript:void(0);">0:15:00 under</FONT></A></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">8:00:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">15:30:00</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"><A onclick="document.formFb995560.sltNb570403.options[4].selected=true; document.formFb995560.txbFb655397.value = document.formFb995560.sltNb570403.value;" href="javascript:void(0);">7:30:00</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center"><A onclick="document.formFb995560.sltNb570403.options[5].selected=true; document.formFb995560.txbFb655397.value = document.formFb995560.sltNb570403.value;" href="javascript:void(0);">0:15:00 over</FONT></A></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet9</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker 2.20] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

The simulation shows the formulas are pretty straight forward. Please do post back if you still need the VBA solution.

Yogi Anand · Apr 14th 2003

Hi Dwayne:

The way it is supposed to work is that you must scroll the list to the top -- that is the top of your list.

Did you try Data Validation in cell A23, where I have set it up as a Dynamic range. From what I gather from our discussion in the posts that will probably suit you best.

Let me know how the use of Data Validation for cell A23 works for you.

Yogi Anand · Apr 14th 2003

Hi lolo70:

Yes, it is possible. That will involve some cutting and pasting, may be even hiding of some rows and columns.

You may also want to look at using the Camera feature in Excel, to take snap shots of different parts of worksheets, then assemble them into one composite presentation.

Yogi Anand · Apr 14th 2003

Hi Dwayne:

From what I have seen in your file, your Data Validation lists are working as they should. However, for DataValidation for cell A23, I have set that up as a Dynamic range for you. See if that helps.

I have also added a note for you to look at. Please post back if it works for you now and let us take it from there.

Yogi Anand · Apr 14th 2003

Hi Dwayne:

When you compose your response for replying to a post, look at the bottom left hand corner of the outer box, and you will see a box for attaching a file with your response. Use the Browse button to navigate to the folder where your file is located, and take it from there.

Yogi Anand · Apr 14th 2003

Hi Dwayne:

I am using Excel97. If I set up DATA|Validation for a cell using the list range A1:A20 (where cells A1:A5 are populated, and a6:a20 are blank). When I use Data Validation, I see the list of 5 names first with the blanks at the bottom of the list.

What version of Excel are you using? How about if you upload your worksheet, so we can have a look at what you are working with and what is causing the problem.

In any event, setting up a dynamic range will resolve the problem, because it will always be presenting to you only the populated part of the list.

Yogi Anand · Apr 14th 2003

Hi LAS:

One way I can think of -- it would be possible if you are using an all CAPS font.

Otherwise I will have to make use of a formula to do it, or use VBA.

Let us see if some on has some other ideas!

Yogi Anand · Apr 14th 2003

Hi Dwayne:

Welcome to the Board!

In your list, where have you entered the blank cells -- are the blank cells scattered in the list, or are these on top of the list? If the blank cells are at the bottom of the list, you should not be experiencing any problem with accessing the list.

As Dave Hawley has already mentioned, if you are going to be continually adding to this list, you should consider setting up a dynamic range. This way you set it up once, and it continues to work whether you add more names, or modify the list in some other way.

Yogi Anand · Apr 14th 2003

Hi Edwin:

I don't know if this is the most efficient way to do it -- but the following code does do the job. Please note that I am using cell C1 to temporarily hold the value of TextBox4, if C1 is occupied, you may have to use another cell and adjust the code accordingly ...

Code

[C1] = TextBox4
    [C1] = Evaluate("=IF(OR(C1={1,21,31}),C1&""st"",IF(OR(C1={2,22}),C1&""nd"",IF(OR(C1={3,23}),C1&""rd"",C1&""th"")))")
    TextBox4 = [C1]

Please post back if it works for you -- otherwise explain a little further and let us take it from there.

Yogi Anand · Apr 14th 2003

Hi gixxer:

for formating TextBox in a UserForm, try ...

for Phone Number ...
TextBox1= Format(TextBox1, "(000)-000-0000")

for Social Security Number ...
TextBox2= Format(TextBox2, "000-00-0000")

for all uppercase text ...
TextBox3= Format(TextBox3, ">")

Yogi Anand · Apr 14th 2003

Hi sedso10:

You can't go wrong with j-walk's material!

In reference to the ratings, rankings, and leap-frogging, when you have populated the worksheet with some real data, you ought to repost the worksheet showing what formulation you have used (If any), the results you got (if any) and what you think are the right results, and then let us take it from there.

Yogi Anand · Apr 13th 2003

Hi Bob Glenn:

Welcome to the Board!

A little clarification regarding what you are working with will certainly help. How about uploading part of the related file as an attachment!

Yogi Anand · Apr 13th 2003

Hi Jaybee:

You may want to try the following code ...

Code

Sub yPintMultiCopyWithNumberIncrementedBy1()
    For i = 1 To 10
        Worksheets("Sheet1").PageSetup.FirstPageNumber = i
        ActiveSheet.PrintOut 'Preview:=True
    Next i
End Sub

I can't say anything about the efficiency of the code. I have set it up fo 10 copies -- try it, and if it works for you, then change to your required number of 1000.

Please post back if it works for you -- otherwise explain a little further and let us take it from there.

Yogi Anand · Apr 13th 2003

Hi Will:

Congratulations -- and all the VERY BEST to <b>Charlotte Elizabeth</b>, mama, and papa!

Posts by Yogi Anand

[Solved] Formulas: VLOOKUP

Data Validation [SOLVED]

Selecting rest of column from ActiveCell to last non-blank r

Selecting rest of column from ActiveCell to last non-blank r

Code to run macro when cell is selected

Time arithmetic

Time arithmetic

Drop down box [SOLVED]

Can we do this?????

Drop down box [SOLVED]

Drop down box [SOLVED]

Drop down box [SOLVED]

Date format

Drop down box [SOLVED]

More Formatting Texboxes

Formating Textbboxes

Help with VBA

Filling a range one cell at a time. I have a weekly workbook

Printing consecutive numbers for Forms

To my daughter