Menu
  1. Accueil
  2. Tech Forum
  3. Programming

Dynamic Range in excel using vb6 [Solved/Closed]

Report
-
 Ravi -
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

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 762
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 & ")"
1
Thank you
Thank you

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

CCM 2942 users have said thank you to us this month

Report

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

Can you give us the complete code - please?
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!