Excel 2010: VBA Question

I've got this macro that was working rather well but for some reason it keeps choking on me now.  Was wondering if someone could take a look at it for me.  Here is the line that it keeps choking on:  
ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(0, -2).Value + ActiveCell.Value - ActiveCell.Offset(0, -1).Value

Open in new window



Sub microk_createbtn()
rembtn
Dim btn As Button
Application.ScreenUpdating = False
ActiveSheet.Buttons.Delete
Dim t As Range

Range("A65536").Select
Selection.End(xlUp).Select
lastrow = ActiveCell.Row

For i = 4 To lastrow
   Set t = ActiveSheet.Range(Cells(i, 14), Cells(i, 14))
   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn
     .OnAction = "starttime"
     .Caption = "Start"
     .Name = "start" & i
   End With
   
   Set tk = ActiveSheet.Range(Cells(i, 15), Cells(i, 15))
   Set btnk = ActiveSheet.Buttons.Add(tk.Left, tk.Top, tk.Width, tk.Height)
   With btnk
     .OnAction = "stoptime"
     .Caption = "Stop"
     .Name = "stop" & i
   End With
   
   Set tcal = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
   Set btncal = ActiveSheet.Buttons.Add(tcal.Left, tcal.Top, tcal.Width, tcal.Height)
   With btncal
     .OnAction = "microkcal"
     .Caption = Chr(133)
     .Name = "date" & i
   End With
   
   
Next i

Application.ScreenUpdating = True

End Sub
Sub rembtn()
 ActiveSheet.Buttons.Delete
End Sub
Sub starttime()
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select
strtx = Now()
ActiveCell.Value = strtx
End Sub
Sub stoptime()
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select

If ActiveCell.Offset(0, -1) <> "" Then
    stpx = Now()
    ActiveCell.Value = stpx
    ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(0, -2).Value + ActiveCell.Value - ActiveCell.Offset(0, -1).Value                     
    ActiveCell.Offset(0, -1).Value = ""
    ActiveCell.Value = ""
    
        Dim pt As PivotTable
        For Each pt In ActiveSheet.PivotTables
            pt.RefreshTable
        Next pt
Else
    MsgBox ("Not Started yet..")
End If

End Sub
Sub microkcal()
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select
ActiveCell.Offset(0, -1).Select
calender.ComboBox1.Value = Sheet3.Range("G2").Value
calender.TextBox2.Value = ""
calender.Show
End Sub

Open in new window

itsmevicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please define "choking"? What error(s) do you get?
0
itsmevicAuthor Commented:
Hi Martin,

This macro is used to track task vs. time.  basically how long does it take to do a certain task.  it records the total time and then graphs it on the spreadsheet.  there is a start and stop button.  you click start and nothing happens, no error.  you click stop and that's when I get the following error:

"Run-time error '13':

Type mismatch

Gives me option to "End" or "Debug"
0
Martin LissOlder than dirtCommented:
Do you know how to use Debug? If not or even if you do here's an article I wrote on debugging. Don't be put off that it says VB6 since most of it applies to VBA.

In any case put a breakpoint on the problem line by clicking in the left-hand margin and when the code gets there run the cursor over the fields you're trying to do math on and see what the values are.
0
FaustulusCommented:
Looking over your code I first thought that the error might hail from your habit of selecting a Range in order to create an ActiveCell, even if the Range = ActiveCell. Actually, ActiveCell is a Range object. So,
Range("A1").Select
ActiveCell.Value = 100
is just the same as Range("A1").Value = 100

In the event of
Range("A1: C10").Select
ActiveCell.Value = 100
the situation is a little more complicated. The Select method will make the first cell of the selected range the ActiveCell. So, the result is equal to
Range("A1").Value = 100

All of this on the side because it turned out that I found a more worthy subject for my suspicion, in particular these two lines,
ActiveCell.Offset(0, -1).Value = ""
ActiveCell.Value = ""
Here you are setting two cells to a String (Text) value. In the next round of running the code you will attempt to add up their value. This will lead to an error #13 because you can't add a string to a number. Note that "" <> 0. "" is a string of no length which is also not the same as an empty cell.

You may not recognize your code, and it isn't tested, either. But I think this function will not have the error you have been complaining about.
Sub StopTime()

    Dim R As Range
    Dim Pt As PivotTable
    
    Set R = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    
    With R
        If .Column > 1 Then
            If .Offset(0, -1) <> "" Then
                .Value = Now()
                .Offset(0, -2).Value = Val(.Offset(0, -2).Value) + _
                                       Val(.Value) - _
                                       Val(.Offset(0, -1).Value)
                .Offset(0, -1).Value = vbNullString
                .Value = vbNullString
                
                For Each Pt In ActiveSheet.PivotTables
                    Pt.RefreshTable
                Next Pt
            Else
                MsgBox ("Not Started yet..")
            End If
        End If
    End With
End Sub

Open in new window

Incidentally, in the process of trying to understand your code I made some insignificant improvements to it. If you like, you may replace the top part of your code, right up to the For ... Next statement with the following,
    Dim Btn As Button, BtnK As Button, BtnCal As Button
    Dim T As Range, Tk As Range, Tcal As Range
    Dim LastRow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    With ActiveSheet
        .Buttons.Delete
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    For i = 4 To LastRow

Open in new window

It seems like a shame just to discard the result of my effort.

One last thing.
"Best practise" recommends to name variables using caps and smalls but to write them in lower case only. Many people only know the second half of this suggestion. Actually, the idea is to catch typos as you type. With the notable (and regrettable) exception of enums, VBA will convert the lower case letters you type to the proper case you declared. If no change takes place you will know immediately that you typed something wrong.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Did any of the above help you?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.