Dynamic Range in excel using vb6

leo - Jun 23, 2010 at 02:50 AM
 Ravi - Feb 9, 2015 at 09:07 AM
Im creating a application in which i want to export my data to excel. Now im directly coming to the problem. i want to use sum function at end of every column. but the problem is the range i want is dynamic. i tried the floowing

.Range(.Cells(3, iCol ), .Cells(rs.RecordCount , iCol)).Name = "Colsum" & iCol

.Cells(rs.RecordCount + 5, iCol + 1).Value= "=Sum("Colsum" & iCol)"

now when i execute this it returns error " Expected: end of statement"

if im removing & iCol from the code and as well as double quotes from the second Colsum then it will work properly. but in my case it is not practical as i want to sum a number of columns using the same code. so please help me to overcome this issue.

Thanks in advance


1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 24, 2010 at 07:06 PM
Not clear what you were trying to do here
.Cells(rs.RecordCount + 5, iCol + 1).Value= "=Sum("Colsum" & iCol)"

since code is not complete it is hard to tell, as forexample what is colsum etc

i would guess you would need to change some thing close to
.Cells(rs.RecordCount + 5, iCol + 1).formula= "=Sum(" & Colsum & iCol & ")"
Thak you for your response.
Eventhough u r not clear, i got the answer to my problem. i used the following

.Cells(rs.RecordCount + 5, iCol + 1).value= "=Sum(" & Colsum & iCol & ")"

now it is working perfect.

For your information Colsum is the name of the dynamic range, iCol is the column no. , and rs is the recordset.

Thanks again.
Can you give us the complete code - please?