excel - 使用用户选择的单元格范围

Question

我正在编写代码以将工作表复制并粘贴到同一工作簿中给定的次数，同时每次将日期增加一天。当我在代码中输入想要增加自我的范围时，我可以这样做，但是一旦我尝试使用用户选择的范围 - 我得到错误 1004。

任何帮助将非常感激

这是我的代码：

Sub IncrementWorksheets() 
    Dim ws As Worksheet
    Dim Count As Integer
    Dim Rng As Range
    Dim myValue As Integer
    'User input for increment value
    Set Rng = Application.InputBox( _
      Title:="Increment your worksheet", _
      Prompt:="Select a cell you want to increment", _
      Type:=8)
    On Error GoTo       
    'Test to ensure User Did not cancel
    If Rng Is Nothing Then Exit Sub
    'Set Variable to first cell in user's input (ensuring only 1 cell)
    Set Rng = Rng.Cells(1, 1)
    MsgBox Rng.Value
    myValue = InputBox("How many time do you want it to increment? Give me the number ")
    Do While Count < myValue
    For Each ws In Worksheets
        LastWs = ws.Name
        'i = ws.Range("C3").Value           >> code works when i type in cell value
                 '>>>>The error occurs here " 1004 Application-defined or Object-defined error
        i = ws.Range("Rng").Value
        If i > j Then
            j = i
        End If

    Next

    Sheets(2).Select
    Sheets(2).Copy After:=Sheets(LastWs)
    'ActiveSheet.Range("C3").Value = j + 1 >> code works when i type in cell value
        ActiveSheet.Range("Rng").Value = j + 1

    Count = Count + 1

    Loop

End Sub

标签： excelvba

Answer 1

使用 Rng.Address，而不是“Rng”。

Sub IncrementWorksheets()
Dim ws As Worksheet
Dim Count As Integer
Dim Rng As Range
Dim myValue As Integer

    'User input for increment value
    Set Rng = Application.InputBox( _
              Title:="Increment your worksheet", _
              Prompt:="Select a cell you want to increment", _
              Type:=8)

    'Test to ensure User Did not cancel
    If Rng Is Nothing Then Exit Sub
    'Set Variable to first cell in user's input (ensuring only 1 cell)
    
    Set Rng = Rng.Cells(1, 1)
    
    MsgBox Rng.Value
    
    myValue = InputBox("How many time do you want it to increment? Give me the number ")
    
    Do While Count < myValue
        For Each ws In Worksheets
            LastWs = ws.Name
            'i = ws.Range("C3").Value           >> code works when i type in cell value
            '>>>>The error occurs here " 1004 Application-defined or Object-defined error
            i = ws.Range(Rng.Address).Value
            If i > j Then
                j = i
            End If

        Next

        Sheets(2).Select
        Sheets(2).Copy After:=Sheets(LastWs)
        'ActiveSheet.Range("C3").Value = j + 1 >> code works when i type in cell value
        ActiveSheet.Range(Rng.Address).Value = j + 1

        Count = Count + 1

    Loop

End Sub