Problem with Excel Formula... Need some help

[Golden Jing]

Dear All, I use VB 2005 and i want to make a Excel report like that. []A B C .... 1 1 2 3 .... 28 or 29 or 30 or 31 2 8 4 5 ..... 4 ... 3 5 3 5 ..... 2 ... . . . . . . N 4 7 9 ..... 5 ... Total: T T T ..... T ... ( T is total from Person1 to PersonN of each day, N is number) As excel formula "=Sum(A1:AN)" So i have to write all of each day but i want to use look for replace it, how can i do ? Can we do any else beside "=Sum(A1:AN)" like "=Sum((1,1):(1,N)) ? Example: For i=1 to X '(X is number of month) wkSheet.Cells(i, N+1) = "=Sum(" & (i, N + 1) & ":" & N &")" Next '(this loop not work) because invalid formula' I think that maybe i descript not clear but hope you see what i mean. Hope it's able to solve. Best Regards

VB.Net

[Dave Kreskowiak]

That forumlas doesn't work in Excel, so it's not going to work from your code either. All you do is put the "Sum(A1:A_n_)" formula in the first row, then "Sum(B1:B_n_) in the next and so on.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008

[Golden Jing]

Thanks Dave, I would like to tell you again about "=Sum(A1:An)" Yes "A1" is first row, then the An is the under row of the first row which we do not respect what its number. But i want to write code to do that with control "For Loop" and need some else to replace "A1:An", "B1:Bn",... in code. Example: For i =1 to An wkSheet.Cells(1, i) = "=Sum(A1:A"& i &")" Next But i want to need some else replace "=Sum(A1:A"& i & ")" to for example: "=Sum("& number &":" & number &")" Is it possible or not ? Can we replace it ? Thanks you again for your help... Best Regards Sovann

VB.Net

[Dave Kreskowiak]

That doesn't make any sense at all. To build a range like this for Excel, is really just simple string manipulation. What number you put in the n placeholder is up to you. Your explanation is impossible to follow. But, no matter what you're trying to say, yes you can build forumlas and put them in the required cells. I just can't figure out what forumlas your trying to build.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008

[Golden Jing]

Ok thanks you so much, Dave. I try to find more solution which easy than that. Thanks again. Wish you good luck, success in work and life. Best Regards, Sovann

VB.Net