Dynamic Range in excel using vb6 [Solved/Closed]

- - Latest reply:  Ravi - Feb 9, 2015 at 09:07 AM
Hello,
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

Leo
See more 

1 reply

Best answer
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
1
Thank you
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 & ")"

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 2943 users have said thank you to us this month

hi,
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?