Defining a Range

[Jacques Labuschagne]

Hi

Hope somebody can help, I'm trying to define a a range that I will be using throughout the program, but the would like to add the Range as a shorter variable. but for some reason this is not working, can somebody please tell me where I'm going wrong?

Sub MainCall()
        Dim inOut As Boolean
        Dim user As Integer
        Dim ws As Worksheet
        Dim rgIO As Range 'Define Log in or Log Out
        
        Set ws = Worksheets("Summary")
        Set rgIO = Range("Z2").Value
        
        If ws.rgIO.rgIO = "1" Then 'User logging in
            'Call Log_In
            MsgBox "User Log in"
        ElseIf ws.rgIO = "2" Then
            'Call Log_Out
            MsgBox "User Log out"
        End If
                                
End Sub

Regards,
Jacques.

 

[Jacques Labuschagne]

Sorry wrong piece of Code, please find below:

Sub MainCall()
Dim inOut As Boolean
Dim user As Integer
Dim ws As Worksheet
Dim rgIO As Range 'Define Log in or Log Out

Set ws = Worksheets("Summary")
Set rgIO = Range("Z2").Value

If ws.rgIO = "1" Then 'User logging in
'Call Log_In
MsgBox "User Log in"
ElseIf ws.rgIO = "2" Then
'Call Log_Out
MsgBox "User Log out"
End If

End Sub

 

[VoG]

Try

Set rgIO = ws.Range("Z2")

 

[Jacques Labuschagne]

Hi

Thanks eventually working, I got another issue now, I'm using a field "Z1" to determine which sheet I need to write the information to, the operator will type a string eg. WV or DVN or MC all this is existing sheets
but I'm not sure what to define the "xxxx" to... Worksheets("xxxx") xxxx will be the string the operator will type in the field. find code below:

Sub Log_In()
Dim wv As Worksheet
Dim rgUS As Range
Dim i As Integer
Dim user As String

Set wv = Worksheets(user)
Set rgUS = Range("Z1").Value
i = 1


While wv.Cells(i, 2).Value <> ""
i = i + 1
Wend
wv.Cells(i, 1).Value = Date
wv.Cells(i, 2).Value = Time
wv.Cells(i, 10).Value = Day(wv.Cells(i, 1).Value)
MsgBox "Lekke werk " & Time & wv

End Sub

Thanks for help, hope you can also answer the above.

Regards
labjac

 

[Norie]

Try this.

    Set rgUS = Range("Z1")
    Set wv = Worksheets(rgUS.Value)