user defined type not defined issue
 

I wrote some basic code to define ranges and then name the ranges. The code
works great when the button is clicked. However, when I type anything in a
cell I get a message "User defined type not defined". I have checked my
references which I'll note below and then show you my procedure.

I commented out the code and I still received the message, so I'm confused
why it keeps poping up.

Any ideas that will help me?

**********references*************
Visual Basic for Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Forms 2.0 Object Library

*********code*************
Sub AssignRange()
Dim rRng As Range
Dim vCols(4, 1) As Variant
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim x As Integer


'================================================
'Set up an array to assign as name to each range
'used in the SumProduct function on the worksheet
'The number is the column, the name refers to the
'column name in the table
'================================================
vCols(0, 0) = 5 'column E
vCols(0, 1) = "SubTeam"
vCols(1, 0) = 6 'column F
vCols(1, 1) = "CurrStatus"
vCols(2, 0) = 7 'column G
vCols(2, 1) = "PlanStatus"
vCols(3, 0) = 19 'column S
vCols(3, 1) = "NextWk"
vCols(4, 0) = 22 'column V
vCols(4, 1) = "SecondWks"

'============================================
'Get the last row of the range and assign the
'first row
'============================================
iLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row - 1
iFirstRow = 24

'================================================
'loop through each column in the array and assign
'the range, and then name the range based on the
'tables column name
'================================================
For x = 0 To UBound(vCols)
Set Rng = Range(Cells(iFirstRow, vCols(x, 0)), Cells(iLastRow,
vCols(x, 0)))
Rng.Name = vCols(x, 1)
Set rRng = Nothing
Next x
MsgBox "Updated"

End Sub

user defined type not defined issue
 

Check out the below lines.. The variables are not delcared....OR you mean
rRng instead of Rng

Set rRng = Range(Cells(iFirstRow, vCols(x, 0)), Cells(iLastRow, vCols(x, 0)))
rRng.Name = vCols(x, 1)

If this post helps click Yes
---------------
Jacob Skaria


"DennisB" wrote:

I wrote some basic code to define ranges and then name the ranges. The code
works great when the button is clicked. However, when I type anything in a
cell I get a message "User defined type not defined". I have checked my
references which I'll note below and then show you my procedure.

I commented out the code and I still received the message, so I'm confused
why it keeps poping up.

Any ideas that will help me?

**********references*************
Visual Basic for Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Forms 2.0 Object Library

*********code*************
Sub AssignRange()
Dim rRng As Range
Dim vCols(4, 1) As Variant
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim x As Integer


'================================================
'Set up an array to assign as name to each range
'used in the SumProduct function on the worksheet
'The number is the column, the name refers to the
'column name in the table
'================================================
vCols(0, 0) = 5 'column E
vCols(0, 1) = "SubTeam"
vCols(1, 0) = 6 'column F
vCols(1, 1) = "CurrStatus"
vCols(2, 0) = 7 'column G
vCols(2, 1) = "PlanStatus"
vCols(3, 0) = 19 'column S
vCols(3, 1) = "NextWk"
vCols(4, 0) = 22 'column V
vCols(4, 1) = "SecondWks"

'============================================
'Get the last row of the range and assign the
'first row
'============================================
iLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row - 1
iFirstRow = 24

'================================================
'loop through each column in the array and assign
'the range, and then name the range based on the
'tables column name
'================================================
For x = 0 To UBound(vCols)
Set Rng = Range(Cells(iFirstRow, vCols(x, 0)), Cells(iLastRow,
vCols(x, 0)))
Rng.Name = vCols(x, 1)
Set rRng = Nothing
Next x
MsgBox "Updated"

End Sub

user defined type not defined issue
 

Jacob,

Thank you for pointing my miss. I corrected this and I continue to get the
message. What puzzles me is if I delete all the code I still get the
message. Have you heard of this problem in your experience?

Please let me know.

"Jacob Skaria" wrote:

Check out the below lines.. The variables are not delcared....OR you mean
rRng instead of Rng

Set rRng = Range(Cells(iFirstRow, vCols(x, 0)), Cells(iLastRow, vCols(x, 0)))
rRng.Name = vCols(x, 1)

If this post helps click Yes
---------------
Jacob Skaria


"DennisB" wrote:

I wrote some basic code to define ranges and then name the ranges. The code
works great when the button is clicked. However, when I type anything in a
cell I get a message "User defined type not defined". I have checked my
references which I'll note below and then show you my procedure.

I commented out the code and I still received the message, so I'm confused
why it keeps poping up.

Any ideas that will help me?

**********references*************
Visual Basic for Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Forms 2.0 Object Library

*********code*************
Sub AssignRange()
Dim rRng As Range
Dim vCols(4, 1) As Variant
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim x As Integer


'================================================
'Set up an array to assign as name to each range
'used in the SumProduct function on the worksheet
'The number is the column, the name refers to the
'column name in the table
'================================================
vCols(0, 0) = 5 'column E
vCols(0, 1) = "SubTeam"
vCols(1, 0) = 6 'column F
vCols(1, 1) = "CurrStatus"
vCols(2, 0) = 7 'column G
vCols(2, 1) = "PlanStatus"
vCols(3, 0) = 19 'column S
vCols(3, 1) = "NextWk"
vCols(4, 0) = 22 'column V
vCols(4, 1) = "SecondWks"

'============================================
'Get the last row of the range and assign the
'first row
'============================================
iLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row - 1
iFirstRow = 24

'================================================
'loop through each column in the array and assign
'the range, and then name the range based on the
'tables column name
'================================================
For x = 0 To UBound(vCols)
Set Rng = Range(Cells(iFirstRow, vCols(x, 0)), Cells(iLastRow,
vCols(x, 0)))
Rng.Name = vCols(x, 1)
Set rRng = Nothing
Next x
MsgBox "Updated"

End Sub