adding cell in excel vba

[rob0923]

Hi,

I am looking to add a range of cells in vba and not in excel. It seems easy enough but I been stuck on this for a while because it won't transfer the value to a word bookmark.

The code I have is as follows. I really don't want to set each range individually A1 + A2 + A3. I would prefer as follows. Not sure why it isn't working.

[VBA]

Private Function Special_Calculation(ByRef objDoc As Object) As Boolean
Dim PlanNos1, PlanNos2

With Worksheets("Sheet1")
PlanNos1 = Application.WorksheetFunction.Sum(Range("I31:J31"))
PlanNos2 = Application.WorksheetFunction.Sum(Range("I32:I42"))
End With

With objDoc.Bookmarks
.Item("PlanNos1").Range.InsertAfter (PlanNos1.Value)
.Item("PlanNos2").Range.InsertAfter (PlanNos2.Value)
End With

End Function[/VBA]

I have even tried PlanNos1 = Application.Sum(Range("I31:J31"))

[Tom527]

Ok without knowing all the details of what exactly you are trying to do. The problem with your coding is the use of your variables:
PlanNos1 = Application.WorksheetFunction.Sum(Range("I31:J31")) is going to assign PlanNos1 a value (ex: 356),
.Item("PlanNos1").Range.InsertAfter (PlanNos1.Value) is (attempting) to put PlanNos1 in the cell 356 (from above example, not a valid cell#). I cannot figure out the need for inserting but if you are trying to put the values in the cell after the cells :
With Worksheets("Sheet1")
Range("k31").Value = Application.WorksheetFunction.Sum(Range("I31:J31"))
Range("i43").Value = Application.WorksheetFunction.Sum(Range("I32:I42"))
End With

would do it

[rob0923]

I think I need to set them to integers.

Basically if cells 1 to 10 have a value of 1 I would like the macro to add all 10 cells and give the variable total of 10 and then pass it to a word bookmark.

[Benzadeus]

I did not get what you are trying to do either.

Maybe something like this?
[VBA]Private Sub Special_Calculation()

Dim PlanNos1 As Long
Dim PlanNos2 As Long

With Worksheets("Sheet1")
PlanNos1 = Application.WorksheetFunction.Sum(Range("I31:J31"))
PlanNos2 = Application.WorksheetFunction.Sum(Range("I32:I42"))
End With

With objDoc
On Error Resume Next
.Bookmarks.Item("PlanNos1").Delete
.Bookmarks.Item("PlanNos2").Delete
On Error GoTo 0

.Bookmarks.Add Range:=.Range(0, 0 + PlanNos1), Name:="PlanNos1"
.Bookmarks.Add Range:=.Range(21, 21 + PlanNos2), Name:="PlanNos2"
End With

Set objDoc = Nothing

End Sub[/VBA]

[rob0923]

Hi,

to make it a little clear is I need to add cells to get a total. At the moment I have to set an individual range for each cell and then add each value together. For example if I dim a1 a2 a3 and a4 as range

with thisworksheets("sheets1")
set a1 = .range("a1")
set a2 = .range("a2")
set a3 = .range("a3")
set a4 = .range("a4")

with objdoc
.bookmarks("bookmark1").range.insertafter (a1 + a2)

this takes a while so I was wondering if it's possible to add using (a1:a10)
so if each cell has 1 as a value it would return a value of 10

a1 = 1
a2 = 1
a3 = 1
etc..

Therefore (a1:a10) = 10

thanks again for your inputs.