Menu
Ask a question Report

Dynamic Range in excel using vb6 [Solved/Closed]

leo - Jun 23, 2010 at 02:50 AM - Latest reply:  Ravi
- Feb 9, 2015 at 09:07 AM
Programming Ask a question Report
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 

3 replies

Best answer
Report Respond to rizvisa1
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 24, 2010 at 07:06 PM
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 & ")"

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1680 users this month

Report
Leo - Jun 26, 2010 at 03:15 AM
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.
Report
Ravi - Feb 9, 2015 at 09:07 AM
Can you give us the complete code - please?
Ask a question
Most popular