Looking for programming advice and understanding errors

[Boblhed]

Hi all, thank you for taking the time to read this.

I am currently working on a code in which an engine can be running in four possible states: On, Off, Automatic and Manual. I am trying to find the total number of hours it is running in manual. The code is as follows:

'START'

Dim run_time As Date
Dim stop_time As Date
Dim auto_time As Date
Dim man_time As Date
Dim man_time_new As Date
Dim total_man_time As Date
Dim row As Integer
Dim col As Integer


Sub Calc()


Dim Sheet1 As Worksheet
Set Sheet1 = ALM.xlxm("Sheet1")


row = 1
total_man_time = 0


Do While row <= 60
col = 1

If Cells(row, col + 3) = "A-Engine Status COS RUN" Then
run_time = Cells(row, col) + Cells(row, col + 1)
ElseIf Cells(row, col + 3) = "A-Engine Status COS STOP" Then
stop_time = Cells(row, col) + Cells(row, col + 1)
ElseIf Cells(row, col + 3) = "A-Auto/Man Status COS AUTO" Then
auto_time = Cells(row, col) + Cells(row, col + 1)
ElseIf Cells(row, col + 3) = "A-Auto/Man Status COS MAN" Then
man_time = Cells(row, col) + Cells(row, col + 1)
End If

If Cells(row - 1, col + 3) = "A-Engine Status COS RUN" Then
total_man_time = total_man_time + man_time - run_time
ElseIf Cells(row - 1, col + 3) = "A-Engine Status COS STOP" Then
total_man_time = total_man_time
ElseIf Cells(row - 1, col + 3) = "A-Auto/Man Status COS AUTO" Then
total_man_time = total_man_time + man_time - auto_time
ElseIf Cells(row - 1, col + 3) = "A-Auto/Man Status COS MAN" Then
man_time_new = Cells(row, col) + Cells(row, col + 1)
total_man_time = total_man_time + man_time_new - man_time
Else
total_man_time = total_man_time
End If

row = row + 1
Loop
End Sub


Sub FindingLastRow()


'PURPOSE: Different ways to find the last row number of a range
'SOURCE: Squarespace - Claim This Domain


Dim sht As Worksheet
Dim LastRow As Long


Set sht = ALM.xlxm("Sheet1")


'Ctrl + Shift + End
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).row

End Sub


'END'

When I run this code, I get a Runtime Error 424, indicating that an Object is required. Please excuse my ignorance, but I have never worked with VBA before. I was wondering what this error means and how I can fix it.

Thanks for your help!

 

[JoeMo]

Is the error with this line:
Set sht = ALM.xlxm("Sheet1")
?
What is the name of the sheet you want to assign to the variable "sht"?

 

[Boblhed]

Hi JoeMo,

Thanks for your reply. I have changed the name, and it gives me the error regardless:

Dim sht As Worksheet
Set sht = ALM.xlxm("2006")

I have also tried:

Dim sht As Worksheet
'Set sht = ALM.xlxm("2006")
Set sht = ActiveSheet

And using that I get a different error (Error 1004 Application-Defined or Object-Define Error)

Again thank you for your help.

 

[Peter_SSs]

Welcome to the MrExcel board!

A few comments.

1. It is not a good idea to use Sheet1 as a variable name as that could be confused with vba's codename for one of the worksheets.
2. My guess is that ALM.xlxm is supposed to be the name of a workbook. If that is so, perhaps the name is actually ALM.xlsm?
3. When referring to a workbook by name, you need to use Workbooks("workbook name")
4. I would recommend using Option Explicit at the top of every module to force you to declare each variable. Some of the issues above would have been highlighted to you already. To automatically get Option Explicit, in the vba window:
Tools - Options - Editor tab - Require Variable Declaration - OK.

So, try something like this to get you past the first obstacle.

Dim wsSheet1 As Worksheet
Set wsSheet1 = Workbooks("ALM.xlsm").Sheets("Sheet1")

 

[Boblhed]

Hi Peter_SSs,

Thank you for your warm welcome and through advice. I have done as you suggested and fixed my mistakes - naming the file extension incorrectly is a new level of stupid I have reached
Originally I never meant to use Sheet1 as a variable name - I just didn't know what I was doing. I have changed it to sht, as I can see is what is generally used.

Thank you very much for your help!

 

[Peter_SSs]

You are welcome.

Post back with specifics if you have further problems.

 

[Boblhed]

Hello again, sorry to be bothering you with this, however I seem to be stuck again. I am getting an error in my code saying I have a Type mismatch. I was wondering where this mismatch is? Am I not adding the date and time properly? I'm not doing any arithmetic with anything that isnt a Date variable. Here's the code below, any help is appreciated:

Dim run_time As Date
Dim stop_time As Date
Dim auto_time As Date
Dim man_time As Date
Dim man_time_new As Date
Dim total_man_time As Date
Dim row As Integer
Dim col As Integer
Dim celltxt As String


Sub Calc()


Dim sht As Worksheet
Set sht = ActiveSheet
Dim LastRow As Long
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).row


row = 2
total_man_time = 0


Do While row <= 100 'LastRow


    celltxt = Cells(row, 4).Text
    celltxtPre = Cells(row - 1, 4).Text
   
    'This section of the code will store the time at which the engine was stopped, started and changed from manual to auto.
    If InStr(1, celltxt, "A-Engine Status COS RUN") Then
        run_time = Cells(row, 1) + Cells(row, 2)
        MsgBox run_time
        MsgBox "RUN"
        
    ElseIf InStr(1, celltxt, "A-Engine Status COS STOP") Then
        stop_time = Cells(row, 1) + Cells(row, 2)
        MsgBox stop_time
        
    ElseIf InStr(1, celltxt, "A-Engine Status COS AUTO") Then
        auto_time = Cells(row, 1) + Cells(row, 2)
        MsgBox auto_time
        
    ElseIf InStr(1, celltxt, "A-Engine Status COS MAN") Then
        man_time = Cells(row, 1) + Cells(row, 2)
        MsgBox man_time
        
    End If
    
    
    'This section determines the previous cell and calculates the time spent running in manual.
    If InStr(1, celltxtPre, "A-Engine Status COS RUN") Then
        total_man_time = total_man_time + DateDiff("h", run_time, man_time)
    ElseIf InStr(1, celltxtPre, "A-Engine Status COS STOP") Then
        total_man_time = total_man_time
    ElseIf InStr(1, celltxtPre, "A-Engine Status COS AUTO") Then
        total_man_time = total_man_time + DateDiff("h", man_time, auto_time)
    ElseIf InStr(1, celltxtPre, "A-Engine Status COS MAN") Then
        man_time_new = Cells(row, col) + Cells(row, col + 1)
        total_man_time = total_man_time + DateDiff("h", man_time_new, man_time)
    Else
        total_man_time = total_man_time
    End If
    
    MsgBox total_man_time
    
    row = row + 1
Loop
MsgBox "Total"
MsgBox total_man_time
End Sub

Thanks!

 

[Peter_SSs]

I have done as you suggested ..

Not quite, you didn't take up my suggestion about Option Explicit.

I am getting an error in my code saying I have a Type mismatch. I was wondering where this mismatch is?

With errors, not only give the full error message but also the line that caused the problem (click Debug when you get the error).
If you 'Debug' & hover your cursor over the 'row' variable name, it should tell you what row caused the problem & you can investigate that row further.

If you still can't resolve it, please post a small set of sample data that has the problem with it, as we don't know what is in your sheet or where. My signature block below has a link for good ways to do that.

My signature block also recommends how to post code with code tags. It makes the code much easier to read & debug & helps if we want to copy the code to our own workbooks to test. I have fixed your last post for the code tags.