How to find certain cells and add up adjacent cells

[bluesheep]

Hi,
I am new to VBA and need some help with making my code work.
What I am trying to do is have other commands print varying matrices of Nx2 in an Nx2 excel table. I want to make a button that when clicked will search the first column for the string "Total" anywhere in the cell and then get all of the values in the next column and add them all up. Next the sum of those values will be printed in a textbox. I'm getting the error "object required" at the Title after Set Title = Cells.Find..
Thanks!

Private Sub Total_Click()
Dim StartRow As Integer
Dim TitleCol As String
Dim ValCol As String
Dim Cell As String
Dim Val As String
Dim ValCell As String
Dim TotalVal As Long
Dim Title As String
StartRow = 4
TitleCol = "S"
ValCol = "T"
TotalVal = 0
Title = "Start"


While Title <> ""


Val = "0"

Set Title = Cells.Find("ERP Total License", After:="S4", LookIn:="UI Calculator", LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=x1Next, MatchCase:=False)
If Title = True Then
Val = Worksheets("UI Calculator").Range(ValCol & Str(StartRow)).Value
If Val <> "0" And Val <> "" Then
TotalVal = TotalVal + CLng(Val)
End If
End If


StartRow = StartRow + 1

Wend


Worksheets("UI Calculator").Range(TotalCalculation).Value = TotalVal
End Sub

[yoslick11]

Not by my home computer, but can see a few things wrong such as the variable Title declared as a string, but used as an object.

Try looping through all cells in S searching for the proper value and adding if found. Such As

Private Sub Total_Click()
Dim StartRow As Integer
Dim TitleCol As String
Dim ValCol As String
Dim Val As String
Dim TotalVal As Long
Dim Title As String
StartRow = 4
TitleCol = "S"
ValCol = "T"
TotalVal = 0
Title = "Start"
Sheets("UI Calculator").Select
While Title <> ""
Title = Range(TitleCol & StartRow).Value
Val = "0"
If Range(TitleCol & StartRow).Value = "ERP Total License" Then
Val = Range(ValCol & StartRow).Value
If Val <> "0" And Val <> "" Then
TotalVal = TotalVal + CLng(Val)
End If
End If
StartRow = StartRow + 1
Wend
Range("A1").Value = TotalVal
End Sub


I just slapped this together and it hasn't been tested.