Excel Macro queries

Question

 

Hi Friends,

 

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.

 

Regards,

Naveen J V

Answer 1

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).

 

Hi Naveen,

 

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

 

Code sample:

Firstly Add Reference to COM component: Microsoft Excel Object Library.

Code Snippet

Imports Microsoft.Office.Interop.Excel

 

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

        End While

 

        MessageBox.Show("Total is " & sum)

        oExcel.Quit()

 

    End Sub

End Class

 

 

Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2919834&SiteID=1

Store each Array element to Excel in consecutive cells

 

 

Regards,

Martin

Answer 2

Hi Naveen,

 

You can use WorkSheet's UsedRange property to get the range that has actual values populated.

 

Let me know if you need more information.

 

Regards,

Shobhit

Answer 3

Hi,

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.

Regards,
Shobhit

---

Shobhit Deep