Application-Defined or object-defined error

[4Potsy]

Hi, First time posting on Mr. Excel. Has been an awesome reference. I am running into an issue. I am a novice with VBA, so bear with me. I am trying to populate a cell in a template with the value from a list of numbers. The number of templates varies, as does the number of values in the list. Here is the code: I am getting an error in on the line highlighted in red. I am running this macro from the Parent Code Sheet.

Any help is much appreciated!

Sub skus()
Dim lastrow As Long
Dim lastsku As Long
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

lastrow = Sheets("Parent Code").Range("D1048576").End(xlUp).Row
lastsku = Sheets("SKU List").Range("A1048576").End(x1down).Row

For isku = 1 To lastrow

If Range("D" & lastrow).Value = "Demand" Then
Sheets("Parent Code").Range("D" & lastrow - 2).Value = Sheets("SKU List").Range("BR" & lastsku).Value
End If

lastsku = lastsku + 1

lastrow = lastrow - 1
Next isku
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
Application.StatusBar = False
End Sub

 

[pcc]

lastsku = Sheets("SKU List").Range("A1048576").End(x1down).Row

looks like you have a "1" rather than an "l" in the last brackets

 

[VoG]

You have a 1 instead of an l

astsku = Sheets("SKU List").Range("A1048576").End(xlDown).Row

 

[RoryA]

I think you might also want xlUp rather than xlDown if you're starting from A1048576...

 

[4Potsy]

Thanks for the quick responses! The change worked; however, the error moved to this loine in the code:

Sheets("Parent Code").Range("D" & lastrow - 2).Value = Sheets("SKU List").Range("BR" & lastsku).Value

 

[RoryA]

Perhaps you could explain what your code is supposed to do. As written it makes no sense to me since your loop variables appear to be using and altering the last row numbers that you found.

 

[4Potsy]

Sure,
I have a list of sku's in column "A" on the "SKU List sheet. I have a template that ranges 25 rows and 13 columns on the "Parent Code" Sheet. The user can identify the number of templates they want to create. After the templates are created, I want them to be able to populate a specific cell (2 above the row "Demand") within the template range with the sku's from the "SKU List". My thought is to work from the bottom up. So if the user created 100 templates, there would be 100 skus that they would put on the "sku List", they could hit "Populate", and all the templates would be updated with a sku ( 1 sku per template) This would eliminate the need to type them in individually.

Hopefully I did an ok job trying to describe.... ?

 

[RoryA]

Not too sure how that relates to your code. I can't see anything that creates templates or switches between templates. Are these templates separate workbooks, or worksheets in a workbook or what?

 

[4Potsy]

So the "create template" is a seperate macro below. The template is created in the Product Code Sheet. I am locking down the formuas, so the users can not change.

Sub copymore()
Dim rws As Long
Dim multiple As Long
Dim c As Long
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
Sheets("Parent Code").Select
ActiveSheet.Unprotect Password:="password"
multiple = InputBox("How many copies do you want to create?", "Insert #")
Sheets("Parent Code").Range("C1") = multiple

With Sheets("Parent Code").Range("C6:BN30")
rws = .Rows.Count + 2
.Resize(rws).Copy Destination:=.Offset(rws).Resize(rws * multiple)
End With

lastrow = Sheets("Parent Code").Range("D1048576").End(xlUp).Row

For iLock = 5 To lastrow

If Range("D" & lastrow).Value = "Demand" Then
Range("E" & lastrow).Select
Range(Selection, Selection.Offset(11, 62)).Select
Selection.Locked = True
Selection.FormulaHidden = False
End If

If Range("D" & lastrow).Value = "New Demand w/Change" Then
Range("E" & lastrow).Select
Range(Selection, Selection.Offset(2, 62)).Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("E" & lastrow).Select
Range(Selection, Range("E" & lastrow).Offset(1, 0)).Select
Selection.EntireRow.Hidden = True



End If

If Range("D" & lastrow).Value = "Current Demand" Then
Range("E" & lastrow).Select
Range(Selection, Selection.Offset(1, 62)).Select
Selection.Locked = True
Selection.FormulaHidden = False
End If

If Range("D" & lastrow).Value = "New Ending Inventory" Then
Range("E" & lastrow).Select
Range(Selection, Selection.Offset(2, 62)).Select

Selection.Locked = True
Selection.FormulaHidden = False

End If


lastrow = lastrow - 1
Next iLock




ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
Application.StatusBar = False

End Sub