CODEWITHSUNDEEP
excelvba
Philip
Philip

Reputation: 3

Defining a range with two variables

I am trying to set a variable to a range that is defined using two other variables.

SchRow and SchCol vary depending on the date, Mnum and Shift entered into the user form. I keep getting the Run-time error '1004': Application-defined or object-defined error and I cannot figure out why as all of the variables are defined. I even tried to convert the column number result from SchCol into a letter and that did not work either. Any help is appreciated, thanks in advance!

    'Define variables to search for scheduled up time
    Dim SchDate As Range
    Dim FDate As String
    Dim SchTime As String
    Dim SchRow As String
    Dim SchCol As String

    FDate = .Range("A" & FirstEmptyRow).Value

    'Search the schedule tab to find the scheduled up time
    With Sheets("Schedule")

        Set SchDate = Sheets("Schedule").Range("C3:W57").Find(FDate, LookIn:=xlValues)
        'MsgBox (SchDate.Address)
        If SchDate Is Nothing Then

            MsgBox ("Date Not Found, re-enter data with correct date")
            GoTo Terminate

            Else

            SchRow = SchDate.Row + MNum
            'MsgBox (SchRow)
            SchCol = SchDate.Column + Shift - 1
            'MsgBox (SchCol)
            SchTime = .Range(SchCol & SchRow).Value * 60
            'This is where I get the Run time error '1004':
                MsgBox (SchTime)

        End If

    End With

The SchRow and SchCol return the correct numbers as expected, but the SchTime errors out. I want to get the value from the cell in the location defined using SchRow and SchCol.

Upvotes: 0

Views: 342

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

SchCol is a number not a letter and Range() expects a letter. Change the variables to Long and use Cells()

'Define variables to search for scheduled up time
Dim SchDate As Range
Dim FDate As String
Dim SchTime As String
Dim SchRow As Long
Dim SchCol As Long

FDate = .Range("A" & FirstEmptyRow).Value

'Search the schedule tab to find the scheduled up time
With Sheets("Schedule")

    Set SchDate = Sheets("Schedule").Range("C3:W57").Find(FDate, LookIn:=xlValues)
    'MsgBox (SchDate.Address)
    If SchDate Is Nothing Then

        MsgBox ("Date Not Found, re-enter data with correct date")
        GoTo Terminate

        Else

        SchRow = SchDate.Row + MNum
        'MsgBox (SchRow)
        SchCol = SchDate.Column + Shift - 1
        'MsgBox (SchCol)
        SchTime = .Cells(SchRow, SchCol).Value * 60
        'This is where I get the Run time error '1004':
            MsgBox (SchTime)

    End If

End With

Upvotes: 1

Related Questions