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!

 

[Excel Jason]

Thank You for the response; Unfortunately, I received another error.

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

Also - Thank you for the Code Paste recommendation! I gave it a try just now, let me know if the visual is better.

 

[6StringJazzer]

What value are you expecting to get into xNum in that line of code? Your range references are a little convoluted and I want to review what you are doing.

Set InRg = Range("D5:E200")


For Each XRg In InRg.Rows

XRg will be the section of one row of the range, so D5:E5, then D6:E6, and so on.

xNum = XRg.Range("B1").Value

Range B1 of that section is relative to where the range starts, so will be the second column, first row. So the cells will be E5, E6, E7, and so on. Is that what you were expecting?

Last of all, what is the value that is being attempted to assign to xNum on the line of code that raises the error? You are getting an error because it is not a number.

 

[Excel Jason]

Ok, so I had some time to review and I'll just come out and say that I'm not sure what I need that variable to be, others on this site were helping me; However, I will outline what end result I'm trying to achieve and flesh out some details hoping it will help and/or allow us to achieve this with formulas over macros?

Goal: I want to take an item number and duplicates it 'x' amount of times based on a number is an adjacent cell.

So essentially, take Item 10221463 and duplicate 104 times, then move on to item 10224680 and duplicate it 104 times and so on....so essentially 17,000+ lines of Item numbers.

Sorry for the ramble; Hopefully restating my desired results help clearify any confusion.

 

[6StringJazzer]

Your original code set InRg to Range("B5:C120"). This matched the data you showed. Your latest code refers columns D:E instead of B:C which does not match the data you are showing. Columns D and E are blank. I am not sure why you took my original working solution and changed it.

 

[Excel Jason]

Hey String,

I shifted a few columns so that's why my referenced columns are now D:E instead of B:C. My understanding is that it shouldn't affect the VBA it just pasted the data in a new location. Is there anything else in the below script that I can adjust to get rid of the error?

Thanks!

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

 

[6StringJazzer]

I shifted a few columns

Your code is very specific to your worksheet layout and the error is most likely related to that. If you are still getting an error, the only possible way to diagnose this is to review the code and also the exact data you are using as it appears on the worksheet. If you show new code but an old version of the worksheet then we'll just be spinning wheels.