reference excel cells in vb

[Jimgarry]

Hi, thanks in advance,
I have created an excel spread sheet from with in vb
I am now attempting to put a formula into the spread sheet
for each column. I am haviang a problem referencing the column I want to put in the formula. I use the column number but I think excel is expecting a b c d ... I do not know how may columms I will have in the spread sheet

her is my code: thanks for any advice
Jim
' Get data from the database and insert
' it into the spreadsheet.
row = 2
strTempDept = rs.Fields("empDept&quot
intRowStart = 2
IntColStart = 3
Do While Not rs.EOF


If rs.Fields("empdept&quot = strTempDept Then
' put data into the spread sheet for each department
For col = 0 To rs.Fields.Count - 1
xlsExcelSheet.Cells(row, col + 1) = rs.Fields(col).Value
Next col
intRowEnd = intRowEnd + 1
row = row + 1
rs.MoveNext
Else
' end of department
intRowEnd = row
row = row + 1
xlsExcelSheet.Cells(row, 1) = "Department Totals"
For col = 3 To rs.Fields.Count - 1

' here is where Im having a problem
xlsExcelSheet.Cells(row, IntColStart) = "=sum(" & col & intRowStart & ":" & col & IntColStart & intRowEnd & &quot"
Next col
row = row + 2

strTempDept = rs.Fields("empDept&quot
intRowStart = row
End If



Loop

 

[Jimgarry]

Just found this on Microsoft Knowledge Base artical 198144
' Converts (row,col) indices to an Excel-style A1:C1 string
Function IndexToString(row As Long, col As Long) As String
IndexToString = ""
If col > 26 Then
IndexToString = Chr(Asc("A&quot + Int((col - 1) / 26) - 1)
End If

IndexToString = IndexToString & Chr(Asc("A&quot + ((col - 1) Mod 26))
IndexToString = IndexToString & row
End Function

Here are a few examples of the conversion: Calling IndexToString() with row=1 and col=26 yields "Z1".
Calling IndexToString() with row=1 and col=27 yields "AA1".
Calling IndexToString() with row=2 and col=52 yields "AZ2".
Calling IndexToString() with row=2 and col=53 yields "BA2".
Calling IndexToString() with row=10 and col=10 yields "J10".

But still looking for other suggestions
Jim