Having an error selecting a worksheet

[bhachug]

Hey guys,
I'm getting stuck trying to run this code as it continues to insist that I have a "Compile error: Expected Function or variable" at the highlighted region. I've tried googling a solution but have come up with nothing. All I want to do is take in a time from a certain cell, run it through a process, then output it to a range of cells. I'm getting an error just trying to select the worksheets, which is especially weird because I tried a fairly similar code at my home computer and it worked. Any help would be very much appreciated.
Gurpreet

Public Function GetShift()
Dim CurrentShiftStart As Date
Dim tempHour As Integer

Dim Linet(6) As Variant
Linet(0) = "B1"
Linet(1) = "PC1"
Linet(2) = "PC2"
Linet(3) = "PC3"
Linet(4) = "PC4"
Linet(5) = "PC5"
Linet(6) = "Graph Page"
Dim iCount As Integer
Dim Line As Worksheet
For iCount = 0 To 5
Set Line = ThisWorkbook.Worksheets(Linet(iCount))
Line.Select

tempHour = Format(ActiveSheet.Range("C10").Select, "hh")
If (tempHour = 23) Or (tempHour < 7) Then
CurrentShiftName = "Shift 1"

ElseIf (tempHour >= 7 And tempHour < 15) Then
CurrentShiftName = "Shift 2"

ElseIf (tempHour >= 15 And tempHour < 23) Then
CurrentShiftName = "Shift 3"

End If

Linet(6).Range("C8").Activate
Selection.Offset(iCount, 0).Select
ActiveCell.Value = CurrentShiftName
Next iCount
End Function

 

[bhachug]

Bump? Any help?

 

[klarowe]

Why are you setting "Line" as a value? I don't see it called out anywhere in the remaining code. Why not just use "ThisWorkbook.Worksheets(Linet(iCount)).Select"??

And if I'm not mistaking, you should be able to condense

Linet(6).Range("C8").Activate
Selection.Offset(iCount, 0).Select
ActiveCell.Value = CurrentShiftName

to

linet(6).Range("C8").offset (icount,0).Value = CurrentShiftName

 

[bhachug]

Why are you setting "Line" as a value? I don't see it called out anywhere in the remaining code. Why not just use "ThisWorkbook.Worksheets(Linet(iCount)).Select"??

And if I'm not mistaking, you should be able to condense

Linet(6).Range("C8").Activate
Selection.Offset(iCount, 0).Select
ActiveCell.Value = CurrentShiftName

to

linet(6).Range("C8").offset (icount,0).Value = CurrentShiftName

Thanks but now it's given me another type of error. It's saying 'Run-time error '9': Subscript our of range". I just started learning VBA the other day so it's appreciated that you were able to help condensing the latter part of the code.

EDIT: From what I understand excel says that I referenced an invalid array element but I'm starting at array element 0 which in my case should send me to select/activate the worksheet labeled B1. But I'm not sure why it isn't.

 

[klarowe]

Thanks but now it's given me another type of error. It's saying 'Run-time error '9': Subscript our of range". I just started learning VBA the other day so it's appreciated that you were able to help condensing the latter part of the code.

EDIT: From what I understand excel says that I referenced an invalid array element but I'm starting at array element 0 which in my case should send me to select/activate the worksheet labeled B1. But I'm not sure why it isn't.

I'm still new to VBA myself... but these guys have helped me a ton so I'm trying to help out where I can. Unfortunately I'm still real shaky on arrays so I think someone else might have to chime in here.

What line is it highlighting when giving you the error?

 

[bhachug]

Thanks but now it's given me another type of error. It's saying 'Run-time error '9': Subscript our of range". I just started learning VBA the other day so it's appreciated that you were able to help condensing the latter part of the code.

EDIT: From what I understand excel says that I referenced an invalid array element but I'm starting at array element 0 which in my case should send me to select/activate the worksheet labeled B1. But I'm not sure why it isn't.

Okay nevermind I've resolved it but am now having an error... I'll see if I can resolve it myself before posting again. Thanks for the help.

EDIT: I actually have no idea how it was resolved because it just started working...

I might have just made the noob error of not opening the workbook with those workbooks labelled lol.