Run-time error 1004: Application-defined or object-defined error

[Warflion]

Hi, I am just started to learn VBA and messing codes around. I got this runtime error with my codes. Here's how it sets up.

A1 to A100 are ID number 1 to 100. B1 to B100 are "values" which all starts at 100. D1 to D100 are randomly generated number working as ID pointer indicating value flow, equation: "=ROUND(RAND(),2)*100". C1 to C100 are copy and paste number from column D so the number remain static.

Following is the code. Thanks in advance.

Sub Test3()
For m = 1 To 17000
Application.ScreenUpdating = True
Application.ScreenUpdating = False
Call CopyPasteSpecial
For n = 1 To 100
If Range("B" & n).Value > 0 Then
x = Range("C" & n).Value
Range("B" & n).Value = Range("B" & n).Value - 1
Cells(x, 2).Value = Cells(x, 2).Value + 1
Else
Range("B" & n).Value = Range("B" & n).Value
End If
Next n
Range("G1").Value = Range("G1").Value + 1
Next m
End Sub

Sub CopyPasteSpecial()
'
' CopyPasteSpecial Macro
'


Range("D1", "D1000").Select
Application.CutCopyMode = False
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[mrhstn]

Although you test to see if the value in B is greater than 0 you then set x to the value of C, which could be 0

Sub Test3()
Dim m As Long, n As Long, x As Long
Application.ScreenUpdating = False
For m = 1 To 17000
    Call CopyPasteSpecial
    For n = 1 To 100
        If Range("B" & n).Value > 0 Then
            x = Range("C" & n).Value ' this is probably returning 0, see below
            Range("B" & n).Value = Range("B" & n).Value - 1
            Cells(x, 2).Value = Cells(x, 2).Value + 1 ' Runtime error here if x is 0, as above
        Else
            Range("B" & n).Value = Range("B" & n).Value
        End If
    Next n
    Range("G1").Value = Range("G1").Value + 1
Next m
Application.ScreenUpdating = True
End Sub


Sub CopyPasteSpecial()
'
' CopyPasteSpecial Macro
'
Range("C1:C1000").Value = Range("D1:D1000").Value
End Sub

 

[Warflion]

Although you test to see if the value in B is greater than 0 you then set x to the value of C, which could be 0

Sub Test3()
Dim m As Long, n As Long, x As Long
Application.ScreenUpdating = False
For m = 1 To 17000
    Call CopyPasteSpecial
    For n = 1 To 100
        If Range("B" & n).Value > 0 Then
            x = Range("C" & n).Value ' this is probably returning 0, see below
            Range("B" & n).Value = Range("B" & n).Value - 1
            Cells(x, 2).Value = Cells(x, 2).Value + 1 ' Runtime error here if x is 0, as above
        Else
            Range("B" & n).Value = Range("B" & n).Value
        End If
    Next n
    Range("G1").Value = Range("G1").Value + 1
Next m
Application.ScreenUpdating = True
End Sub


Sub CopyPasteSpecial()
'
' CopyPasteSpecial Macro
'
Range("C1:C1000").Value = Range("D1:D1000").Value
End Sub

Totally solved it. replace round() with roundup() now its working. THANK YOU.