VBA Runtime Error 91 with String variable

athickett

New Member
Joined
Mar 14, 2019
Messages
3
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

athickett

New Member
Joined
Mar 14, 2019
Messages
3
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

New Member
Joined
Mar 14, 2019
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,437
Members
409,877
Latest member
DDhol
Top