Excel VBA Run-Time Error '1004'

[Excel Jason]

Hello,

I have a macro that takes an item number and duplicates it 'x' amount of times based on a number is an adjacent cell. The macro works fine but I still receive this error once it completes it's run. "Run-time error '1004': Application-defined or object-defined error"

VBA Code:
Sub RepeatData()
'Repeat Rows
Dim XRg As Range
Dim InRg As Range, OutXRg As Range
'A Box Named Repeat Rows will Show
Set InRg = Range("B5:C120")
Set OutXRg = Range("H5")
Set OutXRg = OutXRg.Range("A1")
For Each XRg In InRg.Rows
xValue = XRg.Range("A1").Value
xNum = XRg.Range("B1").Value
OutXRg.Resize(xNum, 1).Value = xValue
Set OutXRg = OutXRg.Offset(xNum, 0)
Next
End Sub

Thank You!

 

[6StringJazzer]

When asking about a runtime error always indicate which line of code is highlighted when you click the Debug button on the error dialog. Technically you cannot have an error occur after code "completes its run" because if it completed, it is no longer running. I don't see anything glaringly obvious in your code that would cause this. Can you paste in a table of your data, instead of a picture of it, so we can try your code on the same data?

 

[Excel Jason]

Ah ofcourse, thank you for that feedback. Here is the portion that is highlighted as in error.

Here is the raw data that the macro is manipulating (i.e. repeat '10221463' 104 times and then move to '10224680' and repeat that 104 times etc...):

Item	Repeat
10221463	104
10224680	104
10224681	104
10184291	104
10239929	104
10239930	104
10242478	104
10245568	104
10249906	104
10250138	104
10267670	104
10233093	104
10233103	104
10238629	104
10239792	104
10251121	104
10268674	104
10157523	104
10178248	104
10178250	104
10178251	104
10203907	104
10204228	104
10220178	104
10220179	104
10239720	104
10245739	104
10250055	104
10250677	104
10252103	104
10252473	104
10252994	104
10253079	104
10253644	104
10255800	104
10267339	104
10267916	104
10267917	104
10268675	104
10269374	104
10136166	104
10137507	104
10145132	104
10147420	104
10147594	104
10165152	104
10205583	104
10208423	104
10215100	104
10215802	104
10220138	104
10220825	104
10231312	104
10231335	104
10231375	104
10231523	104
10232585	104
10232587	104
10232591	104
10232601	104
10234753	104
10234810	104
10234960	104
10235157	104
10235939	104
10236343	104
10236353	104
10236355	104
10237145	104
10238155	104
10238251	104
10238277	104
10238857	104
10240621	104
10240839	104
10240855	104
10244020	104
10246318	104
10249908	104
10250018	104
10250559	104
10252362	104
10203997	104
10235031	104
10246425	104
10246916	104
10248201	104
10248357	104
10249732	104
10250838	104
10254741	104

 

[6StringJazzer]

Your code sets your input range to B5:C120. However, your data ends at row 95. Therefore on row 96, XNum will be 0 and your Resize fails.

You need to use the actual amount of data instead of hardcoding a range. Here is a quick fix that ends the For loop when blank data is encountered.

Also you have undeclared variables. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Sub RepeatData()

   'Repeat Rows
   Dim XRg As Range
   Dim InRg As Range, OutXRg As Range
   Dim xValue As Long
   Dim xNum As Long
   
   'A Box Named Repeat Rows will Show
   Set InRg = Range("B5:C120")
   Set OutXRg = Range("H5")
   Set OutXRg = OutXRg.Range("A1")
   
   For Each XRg In InRg.Rows

      If XRg.Range("A1") = "" Then Exit For
      
      xValue = XRg.Range("A1").Value
      xNum = XRg.Range("B1").Value
      OutXRg.Resize(xNum, 1).Value = xValue
      Set OutXRg = OutXRg.Offset(xNum, 0)
   
   Next

End Sub

 

[Excel Jason]

Thank You for the help!

 

[Excel Jason]

Hello,

Thank you for all your help - clearly, I'm quite the VBA beginner. Your suggestions helped!

However, I now have a new, different error. Hoping to pick your brains!

Data paste above is still what I'm using this script on. Apologies if I leftg out any needed information. Thank you for the help in advance!~

 

[Alex Blakenburg]

The line Dim xValue as Long, assumes that A1 contains a number. If this cell is always going to contain a text value change it to Dim xValue As String.
If it really is going to be a mixture then you might just have to use Dim xValue as Variant.

 

[6StringJazzer]

I see no advantage to even using the variable xValue. It is only assigned once and only referenced once and not used except as a straight assignment. I would avoid using xValue altogether and make your assignment

OutXRg.Resize(xNum, 1).Value = XRg.Range("A1").Value

Then data type doesn't matter.

 

[Excel Jason]

Thank you, where would I incorporate that into my current script?

Sub RepeatData()

'Repeat Rows
Dim XRg As Range
Dim InRg As Range, OutXRg As Range
Dim xValue As Long
Dim xNum As Long


Set InRg = Range("D5:E200")
Set OutXRg = Range("J5")
Set OutXRg = OutXRg.Range("A1")

For Each XRg In InRg.Rows

OutXRg.Resize(xNum, 1).Value = XRg.Range("A1").Value
xNum = XRg.Range("B1").Value
OutXRg.Resize(xNum, 1).Value = xValue
Set OutXRg = OutXRg.Offset(xNum, 0)

Next

End Sub

 

[6StringJazzer]

I recommend using code tags when you post code. That way your original spacing is preserved. Select the code then click the VBA button in the edit controls.

Sub RepeatData()

'Repeat Rows
Dim XRg As Range
Dim InRg As Range, OutXRg As Range
Dim xValue As Long
Dim xNum As Long


Set InRg = Range("D5:E200")
Set OutXRg = Range("J5")
Set OutXRg = OutXRg.Range("A1")

For Each XRg In InRg.Rows

xNum = XRg.Range("B1").Value
OutXRg.Resize(xNum, 1).Value = XRg.Range("A1").Value
Set OutXRg = OutXRg.Offset(xNum, 0)

Next

End Sub