Run-Time Error 13: Type Mismatch

0
votes

I am getting a "Run-Time Error 13: Type Mismatch" on the following macro (debug line is highlighted). My objective is to check row 1 for any quantity greater than 1 to warn the user that the clock is running for an activity, then exit the macro. A number in row 1 means the user has started the clock on an activity, but clock can only run for one activity at a time. I tried using "ON" in row 1...Dim StartOnRow as Text...but I get the same error. Much appreciation for any help!

Sub StartRunningTimer()
        Dim StartOnRow As Integer
        **StartOnRow = Range("a1:bj1")**
        If StartOnRow > 1 Then
        MsgBox ("Please stop the previously started activity")
        If StartOnRow = True Then Exit Sub
        End If
    Worksheets("TimeElapsed").Activate
    nr = ThisWorkbook.Sheets("TimeElapsed").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(nr, 1) = Format(Now(), "m.d.yy h:mm:ss")
        If Not Started Then
        myTime = Time
        Started = True
    Else
        Worksheets("TimeElapsed").Cells(1, 1).Value = "ON"
        Worksheets("Dashboard").Cells(64, 2).Value = "PRESS IS RUNNING"
        Worksheets("Dashboard").Cells(65, 2).Value = "Time Started:  " & Format(Now(), "hh:MM:ss")
        Worksheets("Dashboard").Cells(74, 2).Value = ""
        Worksheets("Dashboard").Cells(75, 2).Value = ""
        Worksheets("Dashboard").Activate
    End If
End Sub
2
excelvba

2 Answers

0
votes

I think you need a Set statement:

Set StartOnRow = Range("a1:bj1")

Also, Range returns a Range object, not an integer:

Dim StartOnRow AS Range
Set StartOnRow = Range("a1:bj1")
0
votes

If you're looking for just anything greater than one in any of those cells just use a helper cell in BK1 that sums the entire row. Check that cell, StartOnRow = Range("BK1").Value

As for your question, @roryap is correct. When using Range like that, you're getting an object and need to set but that still doesn't get what you want because you would need to loop through the range then to find a value > 1.

Dim StartOnRow as Range
Dim cellCheck as Range

Set StartOnRow = Range("A1:BJ1")

For Each cellCheck in StartOnRow
     if cellCheck.value > 1 Then
          'Stop
          'Exit Sub
     End if
Next cellCheck