Need to loop??Help 2

[zishan619]

Hi I have the following statement:
strH(1) = strH(1) & &quot;<TR>&quot; & vbcrlf
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc nowrap>Total Hours for &quot; & CCNum & &quot; </TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(B2:B&quot;& intRowCount & &quot</TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(C2:C&quot;& intRowCount & &quot</TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(D2&quot;& intRowCount & &quot</TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(E2:E&quot;& intRowCount & &quot</TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(F2:F&quot;& intRowCount & &quot</TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(G2:G&quot;& intRowCount & &quot</TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(H2:H&quot;& intRowCount & &quot</TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(I2:I&quot;& intRowCount & &quot</TH>&quot;
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(J2:J&quot;& intRowCount & &quot</TH>&quot;

Response.Write strH(1)
The document opens up in excel in an iframe. Now I need it to keep totaling the sums of each column depending on how many there are. It could be five columns to hundred columns. I am doing this on a ASP page.
Thanks

 

[woja]

Try this:

   strH(1) = strH(1) & &quot;<TR>&quot; & vbcrlf
   strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc nowrap>Total Hours for &quot; & CCNum & &quot; </TH>&quot;
   ' Set colCount to the number of columns you want
   For i = 1 To colCount
      strCol = Char(Asc(&quot;A&quot;) + i)
      strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(&quot; & strCol & &quot;2:&quot; & strCol & intRowCount & &quot;)</TH>&quot;
   Next i

This should do what you want.

[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[zishan619]

Hi thank you it looks like it is picking up stuff but the columns are incorrect bascially giving me
=sum(662:665) in each column. it should be =sum(A2:A5)
For i = 1 To intRowCount
strCol = CStr(Asc(&quot;A&quot + i)
Response.Write &quot;<TH bgcolor=#cccccc> =sum(&quot; & strCol & &quot;2:&quot; & strCol & zRowCount & &quot</TH>&quot;
Next
I think the strCol are getting numbers instead of Letters.
It doesn't like Char. So I used CStr.

 

[zishan619]

I figured it out thanks it was Chr. Cool

 

[woja]

Yeah, sorry, typo in the original post.
Glad to know all is working.

[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[dbMark]

What if you go past the 26th column &quot;Z&quot;? This example rolls over from Z to AA and from AZ to BA, etc.

strH(1) = strH(1) & &quot;<TR>&quot; & vbcrlf
strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc nowrap>Total Hours for &quot; & CCNum & &quot; </TH>&quot;
' Set colCount to the number of columns you want
For i = 2 To colCount
   strCol = &quot;&quot;
   iInt = int((i-1)/26)
   iMod = i-(iInt*26)
   If iInt > 0 Then
      strCol = Chr(64 + iInt)
   End If
   strCol = strCol & Chr(64 + iMod)
   strH(1) = strH(1) & &quot;<TH bgcolor=#cccccc> =sum(&quot; & strCol & &quot;2:&quot; & strCol & intRowCount & &quot;)</TH>&quot;
Next

Sorry, but I couldn't find a modulus function to get the remainder so I did it the long way with basic math. - dbMark

 

[woja]

Yeah, missed that.

PS. The [tt]Mod[/tt] operator does modulus in VB:

   chrCol = ""
   If (i > 26) Then
       strCol = Chr(64 + Int((i - 1) / 26))
   End If
   strCol = strCol + Chr(64 + (i Mod 26))

[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[zishan619]

Hey Cool Thanks Mark. I wanted to know does anyone know how to make the columns automatically autofit when the excel document opens up. Cool Thanks