bluesheep
06-27-2014, 08:50 AM
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
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