Posts by daniB

daniB · Feb 11th 2004

hmmmmm no maybe Im being a bit dappy!
In VBA how do you define a dynamic range?
I recorded a macro to define a named range using the OFFSET function...I got this....

ActiveWorkbook.Names.Add Name:="xRange", RefersToR1C1:= _
"=OFFSET(InputData!R1C1,1,0,COUNTA(InputData!C1)-1,2)"

Is this how I declare it and then maybe use a loop to decide how many columns to find?

Oh dear Oh dear!!! Any Ideas??

Dani

daniB · Feb 10th 2004

hmmmmm..I can see where you are comin from....
But the number of hidden columns changes each week..........
Am I going to have to writre some VBA for this??

daniB · Feb 10th 2004

OK thank you for the code getting a range into word thats brilliant!
I do however havea slight issue which I forgot to mention!
With the OFFSET function you provided it selects the last two columsn perfectly!

However, I have columns hidden and this OFFSET function selects the very last two columns that are hidden...I would like to select the last two columns which are not hidden! DOH!

Any ideas??

Thank you tho, I am finally getting somewhere with this!

Dani

daniB · Feb 9th 2004

Thank you!!! I await eagerly!!!!!

daniB · Feb 9th 2004

Hellooooo All!

Here is an example of my spreaddie.....
<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=6><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - TSP against volumes v0.3.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2002 XP : OS = Windows Windows 2000 </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=6><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=formCb755237><INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980></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=6><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION value="=Networks!C39+'WTSG Core'!C35+'WTSG Impact'!C38" selected>C5<OPTION value="=Networks!D39+'WTSG Core'!D35+'WTSG Impact'!D38">D5<OPTION value="=Networks!E39+'WTSG Core'!E35+'WTSG Impact'!E38">E5<OPTION value="='WTSG Impact'!C42">C6<OPTION value="='WTSG Impact'!D42">D6<OPTION value="='WTSG Impact'!E42">E6<OPTION value="='WTSG Core'!C39">C7<OPTION value="='WTSG Core'!D39">D7<OPTION value="='WTSG Core'!E39">E7<OPTION value==Networks!C43>C8<OPTION value==Networks!D43>D8<OPTION value==Networks!E43>E8<OPTION value==SUM(C6:C8)>C9<OPTION value==SUM(D6:D8)>D9<OPTION value==SUM(E6:E8)>E9<OPTION value==C9/C5>C10<OPTION value==D9/D5>D10<OPTION value==E9/E5>E10</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value="=Networks!C39+'WTSG Core'!C35+'WTSG Impact'!C38" name=txbFb426622></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><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>E</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 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left">All Teams - Productivity Report</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> </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-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">30/06/2003</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">07/07/2003</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">14/07/2003</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-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </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-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: left">Load</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right"> </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>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Volumes</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">All Teams</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[0].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">111</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[1].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">115</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[2].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">170</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>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">WTSG - Impact</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[3].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">243</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[4].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">242.5</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[5].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">226</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>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">WTSG - Core</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[6].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">52</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[7].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">63.5</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[8].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">51</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>8</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Networks</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[9].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">139.5</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[10].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">132.25</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[11].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">149</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>9</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">Total</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[12].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">434.5</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[13].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">438.25</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[14].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">426</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>10</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #ff0000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #ff0000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">Time/Volume</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #ff0000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[15].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">3.91</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #ff0000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[16].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">3.81</FONT></A></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #ff0000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"><A onclick="document.formFb078704.sltNb935705.options[17].selected=true; document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value;" href="#javascript:void(0);">2.51</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>11</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Cumulative Volume</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </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>12</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 1.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">Cumulative Hrs</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-STYLE: italic; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </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>13</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; 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: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </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>14</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 8pt; 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: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </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=6><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>Total Load</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker 2.41] </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>

Ok.....each week I add a column on the end with that curretn weeks data. I then need to get the last two weeks data (i.e.Column D and E) and Column A only into a word document.

Is it possible to use the OFFSET function to name this specific dynamic range...and can i use VBA to select the last two weeks data?
Also there is the problem of getting this into a word Document...Ive had a little experience wiv how to get data from excel into word...but its all a blur to me!!

If anyone can help or has any suggestions I would be eternally GRATEFUL!!!!!

Thankyou for reading!!

Dani

daniB · Jan 29th 2004

AHAHA !!!!!!! The simple things!! Anyway then that code should help you clear contents FOR ALL Worksheets in a work book!!!!
Sorted!
:wink1:

daniB · Jan 29th 2004

I am trying to do pretty much the same thing......but I want to clear the contents of everything in each worksheet.....Ive been trying this route...
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
Cells.Select
Selection.ClearContents
Next ws

It only clears one worksheet......If this is your aim as well, to clear all worksheets then this may help, but I still need to iron out my bug...it only clears one worksheet...Maybe Im onto the wrong track?!!!?

daniB · Jan 28th 2004

Im trying yo update all the charts in my workbook with the same format.......
I thought this may work but it hasnt.......Anyone got any ideas what Im doin wrong???? THANKS!!!!!!!!!!!!!!1

Sub Charts()
'

'

'
Dim chartn As Chart
For Each chartn In ActiveWorkbook.Charts

ActiveSheet.ChartObjects("chartn").Activate

ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(3).DataLabels.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(4).DataLabels.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Italic"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Italic"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Legend.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Next chartn
End Sub

daniB · Jan 22nd 2004

Thanks for the redirection!! I didnt pick it up cos of the title!

If I input the date 01/22/04
I get the error message:

[Microsoft] [ODBC SQL Server Driver] [SQL Server]Line 3: Incorrect syntax near ‘2004-01-22 00:00:00’.

I have no idea what I am doin wrong!!!!!

Any Ideas?

Sub GenerateForm()

Dim dat As Date, result As Boolean
Dim cutdate As String, n As Long
With EnterDate
.dat1 = DateSerial(Year(Now), Month(Now), Day(Now))
.InsrtDate = CStr(.dat1)
.SpinButton1 = 0
.Label1 = "Please select the date from which you wish to get the data"
.Show
If .result = False Then Exit Sub
Application.ScreenUpdating = False
End With

finaldat = EnterDate.InsrtDate
finaldat = Format(CStr(finaldat), "yyyy-mm-dd hh:00:00")

If EnterDate.result = True Then

ActiveWorkbook.Worksheets.Add

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=ServiceCenterPROD;Description=Service Center Production Database;UID=svcprod;PWD=*******;APP=Microsoft Office XP;WSID=AC6S" _
), Array("2BKN9ZW1B8")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT probsummarym1.number, probsummarym1.open_time, probsummarym1.close_time, probsummarym1.resolution, probsummarym1.brief_description, probsummarym1.resolved_by, probsummarym1.resolved_time, probs" _
, _
"ummarym1.acc_subservice, probsummarym1.acc_outage_start, probsummarym1.acc_outage_end, probsummarym1.company, probsummarym1.severity_code" & Chr(13) & "" & Chr(10) & "FROM ServiceCenter.dbo.probsummarym1 probsummarym1" & Chr(13) & "" & Chr(10) & "WHERE (pr" _
, _
"obsummarym1.open_time>=ts '" & finaldat & "') AND (probsummarym1.resolution Like '%NETWORKS%') AND (probsummarym1.severity_code='1')" & Chr(13) & "" & Chr(10) & "ORDER BY probsummarym1.close_time" _
)
.Name = "Query from ServiceCenterPROD"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With

End If

If I input the date 01/22/04
I get the error message:

[Microsoft] [ODBC SQL Server Driver] [SQL Server]Line 3: Incorrect syntax near ‘2004-01-22 00:00:00’.

daniB · Jan 22nd 2004

For some reason it only selects the active cell and the one cell above it!!!

daniB · Jan 22nd 2004

The starting cell will always be the active cell......

As long as there is data in the cell I want it to be included in my new highlighted range.....

Have I missed anything out thats important?

Dani

daniB · Jan 22nd 2004

Hiya Everyone!

I maybe going about this in a very convoluted way.......

Im trying to highlight/select a variable range of cells....So far I have this code which highlights one column depending on the length of the column (data filled).

Sub SelectColumn()

Dim startCell As Range, cell1 As Range, cell2 As Range
Dim rowNr&, colNr&

Set startCell = ActiveCell
rowNr = startCell.Row: colNr = startCell.Column

If IsEmpty(startCell) Then Exit Sub

For rowNr = startCell.Column To 1 Step -1
If IsEmpty(Cells(rowNr, colNr).Value) Then
Set cell1 = Cells(rowNr + 1, colNr)
Exit For
End If
Next rowNr

If cell1 Is Nothing Then Set cell1 = Cells(1, colNr)

For rowNr = startCell.Row To 500
If IsEmpty(Cells(rowNr, colNr).Value) Then
Set cell2 = Cells(rowNr - 1, colNr)
Exit For
End If
Next rowNr
If cell2 Is Nothing Then Set cell2 = Cells(500, colNr)

Range(cell1, cell2).Select

End Sub

I think it is probably staring me in the face...or there is a really quick way of doing this...Can anyone help??

Thanks!
:cool:

daniB · Jan 22nd 2004

Does anyone know if it is possible to import data from an SQL database using a given variable.

I.e. the user inputs a date they wish to get the data frmo and to. (from 1st Novemeber to 15th november). This date is stored as a date variable. Using this variable is it possible to import data from the database given this date scale?

for example:

Dim dat as date

(code for importing data using querytables.add)
.CommandText = ARRAY(SELECT table1.name, table1.address, table1.date
FROM databasey.table1 WHERE table1.date >= ts '2003-11-01 00:00:01'})

Is it possible to replace the "ts '2003-11-01 00:00:01" (I am also unsure what the 'ts' bit means) with the varibale 'dat'. If it is possible what format does the date have to be in?

I am stuck in a rut here

Posts by daniB

Access : VBA Getting a dynamic range into Word document on a

Access : VBA Getting a dynamic range into Word document on a

Access : VBA Getting a dynamic range into Word document on a

Access : VBA Getting a dynamic range into Word document on a

Access : VBA Getting a dynamic range into Word document on a

VBA : clearing a sheet

VBA : clearing a sheet

Excel Charts changing to a specific format

VBA : Importing Data from SQL database - variables

VBA : Excel selecting/highlighting a variable range

VBA : Excel selecting/highlighting a variable range

VBA : Excel selecting/highlighting a variable range

VBA : Importing Data from SQL database - variables