定义具有两个变量的范围

菲利普

我正在尝试将变量设置为使用其他两个变量定义的范围。

SchRow 和 SchCol 因输入到用户表单中的日期、Mnum 和 Shift 而异。我不断收到运行时错误 '1004': Application-defined or object-defined 错误,我无法弄清楚为什么定义了所有变量。我什至试图将来自 SchCol 的列号结果转换为一个字母,但这也不起作用。任何帮助表示赞赏,提前致谢!

    '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

SchRow 和 SchCol 按预期返回正确的数字,但 SchTime 出错。我想从使用 SchRow 和 SchCol 定义的位置的单元格中获取值。

斯科特·克雷纳

SchCol是一个数字而不是一个字母,Range()需要一个字母。将变量更改为Long并使用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

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章