VBA Runtime Error 91 with String variable

[athickett]

Hello,

I have had this code working with the text manually entered into the code and I have tested the code in a separate macro on its own but when i put it together it give me this error. I understand it is due to something not having a value set but as it happens on the line setting the value of a string I don't know what it can be.

The error occurs if I run the code to the line after 'create part name.

Previously I ran the code with the "Part" & Part_N & "_Name" replaced with "A456" or whatever the cell address I wanted to use. Everywhere the cell is calculated from the Start_Cell I also had hard coded and the code ran with no errors.

Any help would be great as i'm puzzled, thanks.


Sub Part_Add()


Dim cell As Range
Dim rng As Range
Dim RangeName As String
Dim CellName As String
Dim Start_Cell As String
Dim Rng_Copy As String
Dim Part_N As String


Start_Cell = ActiveCell.Address
Rng_Copy = Start_Cell & ":" & Range(Start_Cell).Offset(13, 12).Address
Part_N = Range("Next_Part").Value


'The Range.Copy Method - Copy & Paste with 1 line
Range(Rng_Copy).Copy 'Copy Range (A:M+13)
Range(Rng_Copy).Offset(15, 0).PasteSpecial xlPasteAll 'Paste Range (A:M+13)
Range(Start_Cell).Offset(15, 0).Value = "Part " & Part_N 'Change Part Name (A+15)
Range(Start_Cell).Offset(15, 0).ListObject.Name = "Part" & Part_N & "_Table" 'Rename Table (A+16)


'Single Cell Reference (Workbook Scope)


'Create Part_Name
RangeName = "Part" & Part_N & "_Name"
CellName = Range(Start_Cell).Offset(15, 0).Address 'A+15

Set cell = Worksheets("Manufacturing Times").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell


'Create Part_Time
RangeName = "Part" & Part_N & "_Time"
CellName = Range(Start_Cell).Offset(28, 5).Address 'F+28

Set cell = Worksheets("Manufacturing Times").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell


'Create Part_Cost
RangeName = "Part" & Part_N & "_Cost"
CellName = Range(Start_Cell).Offset(28, 6).Address 'G+28

Set cell = Worksheets("Manufacturing Times").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell


'Create Part_CGL_Cost
RangeName = "Part" & Part_N & "_CGL_Cost"
CellName = Range(Start_Cell).Offset(22, 9).Address 'J+22

Set cell = Worksheets("Manufacturing Times").Range(CellName)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell

End Sub

 

[athickett]

Hey,

This was my test code for info.

Sub Cell_Test()


Dim Start_Cell As String
Dim Rng_Copy As String
Dim Part_N As String
Dim RangeName As String


Start_Cell = ActiveCell.Address
Rng_Copy = Start_Cell & ":" & Range(Start_Cell).Offset(13, 12).Address
Part_N = Range("Next_Part").Value
RangeName = "Part" & Part_N & "_Name"


MsgBox "Value is " & Rng_Copy & " - " & RangeName


End Sub

 

[athickett]

I have fixed this myself

Range(Start_Cell).Offset(15, 0).ListObject.Name = "Part" & Part_N & "_Table" 'Rename Table (A+16)

This offset should have been (16,0) as it the table doesn't exist above offset 16

Adam