Thursday, February 28, 2008 1:31 PM
I have some quires related to Microsfot Excel macro. I have a set of range values starting from a static cell. The end cell depends on the data available. I wanted to calculate sum of these range which is dynamic always(from cell is fixed but to cell is dynamic). Please let me know if u have any solutions.
Naveen J V
Wednesday, March 05, 2008 3:29 AM
Naveen J V, Bangalore wrote:
I have some quires related to Microsfot Excel macro. I have a set of range values starting from a static cell. The end cell depends on the data available. I wanted to calculate sum of these range which is dynamic always(from cell is fixed but to cell is dynamic).
The key to solving your question is as below:
oSheet.Range("A1").Offset(0, 1) 'move to Cell B1. Move to next cell horizontally
oSheet.Range("A1").Offset(1, 0) 'move to Cell A2. Move to next cell vertically
Firstly Add Reference to COM component: Microsoft Excel Object Library.Code Snippet
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oExcel As Object = CreateObject("Excel.Application")
Dim oBook As Object = oExcel.Workbooks.Open("C:\Book.xls")
Dim oSheet As Object = oBook.Worksheets(1)
Dim sum As Double = 0
Dim CurCell As Range = oSheet.Range("A1") ' e.g. Start Cell is A1
While CurCell.Value <> Nothing
sum += CurCell.Value
CurCell = CurCell.Offset(0, 1) ' Sum cells value horizontally
MessageBox.Show("Total is " & sum)
Store each Array element to Excel in consecutive cells
Friday, April 04, 2008 6:49 AM
You can use WorkSheet's UsedRange property to get the range that has actual values populated.
Let me know if you need more information.
Tuesday, June 24, 2008 6:55 AMHi,
As I generally feel more comfortable in working with collections, another implementation for the same is like this:
1 Public Sub prTemp() 2 Dim objRange As Range 3 Dim objStartCell As Range 4 Dim objEndCell As Range 5 Dim objValueCell As Range 6 Dim lngSum As Long 7
' >> Provide Row And Col Index Of The Fixed Cell Here
8 Set objStartCell = Sheet1.Cells(7, 4) 9 ' >> Get End Cell 10 Set objRange = Sheet1.UsedRange
' >> xlDown Is Provided If The Range Goes Downwards, Give xlRight Is Range Grows Horizontally
11 Set objEndCell = Sheet1.Cells(objRange.End(xlDown).Row, objRange.End(xlDown).Column) 12 ' >> Get Sum 13 Set objRange = Sheet1.Range(objStartCell, objEndCell) 14 For Each objValueCell In objRange 15 lngSum = lngSum + Val(objValueCell.Value) 16 Next 17 Debug.Print lngSum 18 End Sub
Although this code is a VBA implementation, you can use the same stuff with .Net also.
- Marked As Answer by Martin Xie - MSFT Wednesday, June 25, 2008 3:16 AM