A subroutine that I have used for a long time has stopped working for some reason. The simplified macro below causes a "run time error '6': Overflow" when I try to assign a value to a cell. The value in cell C2 is assigned to cValue but the error occurs when it is set in cell C3.

Private AWB As Workbook
Private cValue As Double

Sub Test()

    Set AWB = ThisWorkbook
    Sheets("Import").Select

    Range("C2").Select
    cValue = ActiveCell.Value

    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = cValue

End Sub

CodePudding user response：

Sub Solution1()

    'Set AWB = ThisWorkbook
    
    With Worksheets("Import").Range("C2")
        cValue = .Value2
        .Offset(1, 0).Value2 = cValue
    End With
    
End Sub
    ' *** <OR> ***
Sub Solution2()
    With Worksheets("Import").Range("C2")
        .Offset(1, 0).Value2 = .Value2
    End With
End Sub

If you still run into trouble, try changing your cValue type to as variant.

CodePudding user response：

Copy Cell Value

• If you're dealing with the workbook containing this code, specify it with ThisWorkbook where ever you need it. If it's in a hundred procedures, do it a hundred times.
• Using variables outside of procedures is kind of a last resort when you can't handle things differently, not to write fewer characters.
• Qualify your objects by using variables (instead of using Select or any flavors of Active) to avoid long, unreadable expressions like e.g. ThisWorkbook.Sheets("Import").Range("C2").Offset(1).Value = ThisWorkbook.Sheets("Import").Range("C2").Value which you could actually use instead of the posted code (not recommended).

Option Explicit

Sub Test()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Import")
    
    Dim sCell As Range: Set sCell = ws.Range("C2")
    Dim dCell As Range: Set dCell = sCell.Offset(1)
    
    dCell.Value = sCell.Value
    
End Sub