Compile Error: Variable Not Defined

P

pawcoyote

Board Regular
Joined
Mar 5, 2012
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Howdy,

I have used this code before for other sheets in my workbook. I changed the name to be the new worksheet and adjusted the code throughout my workbook to include the new worksheet.

I get the Compile Error: Variable Not Defined when ever I point to the Set wsIBMass (new worksheet). I am not sure what I am missing.

Code: 
Option Explicit


'Created on 11/27/17; Copy of information from MDS Equipment Detail Sheet to the IB Mass Creation Sheet


Sub BuildIBMass()
    Dim i As Long
    Dim iCol As Long
    
    Set wsMDS = Worksheets("MDS Equipment Detail")
    Set wsIBMass = Worksheets("IB Mass Creation")
    
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    Set headerIBMass = wsMOST.Rows(rowHeaderIBMass)
    
    Application.ScreenUpdating = False
    
    'get last Client Name row
    Set wsMDS = Worksheets("MDS Equipment Detail")
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    
    'get last Client Name row
    iCol = GetColumnNumber("Client Name", headerMDS)
    
    With wsMDS
        rowDataEndMDS = .Cells(.Rows.Count, iCol).End(xlUp).row
    End With
    
    'added 11/27/2017
    If rowDataEndMDS < 7 Then
        MsgBox "No Data on MDS to copy to MOST"
        Exit Sub
    End If
    
    Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)


    Range(wsIBMass.Rows(rowDataStartIBMass), wsMOST.Rows(rowDataEndMDS)).EntireRow.Delete
    
    'Fields to be filled in when Copy from MDS to IB Mass Creation macro is enabled
    
    For i = rowDataStartMDS To rowDataEndMDS
        With wsIBMass.Rows(i - 2)
'            LUV(headerMOST, "Config Serial Number" & vbLf & "Manufacturer serial if no Configured serial", i - 2).Value = LUV(headerMDS, "Oracle Configuration SN", i).Value
            
            .Cells(2).Value = LUV(headerMDS, "Serial Number", i).Value
            .Cells(16).Value = LUV(headerMDS, "Ricoh Equipment ID", i).Value
            .Cells(21).Value = LUV(headerMDS, "Department Name (if required)", i).Value
            .Cells(22).Value = LUV(headerMDS, "Cost Center (if required)", i).Value
            .Cells(45).Value = LUV(headerMDS, "IP Address", i).Value
            .Cells(50).Value = LUV(headerMDS, "MAC Address", i).Value
            
            
        End With
    Next i


    Application.ScreenUpdating = True


    MsgBox "The data has been copied to the IB Mass Creation worksheet.  Please verify that the data has copied over properly!"
    


End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
You have at least 5 variables not declared, easiest way out of it is to comment out the Option Explicit line (I would declare them if I wasn't on my phone).
 
Upvote 0
Now I am in, without removing the Option Explicit your declarations look like they should be (untested)...

Rich (BB code): 
Option Explicit




'Created on 11/27/17; Copy of information from MDS Equipment Detail Sheet to the IB Mass Creation Sheet


Sub BuildIBMass()
    Dim i As Long, iCol As Long, wsMDS As Worksheet
    Dim wsIBMass As Worksheet, wsMOST As Worksheet
    Dim rowDataEndMDS As Long, rMDS As Range
    Dim rowHeaderMDS As Long, rowHeaderIBMass As Long
    Dim rowDataStartMDS As Long, rowDataStartIBMass As Long
    Dim headerIBMass As Range, headerMDS As Range
    
    
    Set wsIBMass = Worksheets("IB Mass Creation")
    Set headerIBMass = wsMOST.Rows(rowHeaderIBMass)
    
    Application.ScreenUpdating = False
    
    'get last Client Name row
    Set wsMDS = Worksheets("MDS Equipment Detail")
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    
    'get last Client Name row
    'iCol = GetColumnNumber("Client Name", headerMDS)
    
    With wsMDS
        rowDataEndMDS = .Cells(.Rows.Count, iCol).End(xlUp).Row
    End With
    
    'added 11/27/2017
    If rowDataEndMDS < 7 Then
        MsgBox "No Data on MDS to copy to MOST"
        Exit Sub
    End If
    
    Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)


    Range(wsIBMass.Rows(rowDataStartIBMass), wsMOST.Rows(rowDataEndMDS)).EntireRow.Delete
    
    'Fields to be filled in when Copy from MDS to IB Mass Creation macro is enabled
    
    For i = rowDataStartMDS To rowDataEndMDS
        With wsIBMass.Rows(i - 2)
'            LUV(headerMOST, "Config Serial Number" & vbLf & "Manufacturer serial if no Configured serial", i - 2).Value = LUV(headerMDS, "Oracle Configuration SN", i).Value
            
            .Cells(2).Value = LUV(headerMDS, "Serial Number", i).Value
            .Cells(16).Value = LUV(headerMDS, "Ricoh Equipment ID", i).Value
            .Cells(21).Value = LUV(headerMDS, "Department Name (if required)", i).Value
            .Cells(22).Value = LUV(headerMDS, "Cost Center (if required)", i).Value
            .Cells(45).Value = LUV(headerMDS, "IP Address", i).Value
            .Cells(50).Value = LUV(headerMDS, "MAC Address", i).Value
            
            
        End With
    Next i


    Application.ScreenUpdating = True


    MsgBox "The data has been copied to the IB Mass Creation worksheet.  Please verify that the data has copied over properly!"
    


End Sub
 
Upvote 0
Hi,

It failed on Set headerMDS this time...

Code: 
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    Set headerIBMass = wsMOST.Rows(rowHeaderIBMass)

Code: 
Sub BuildIBMass()
    Dim i As Long, iCol As Long, wsMDS As Worksheet
    Dim wsIBMass As Worksheet, wsMOST As Worksheet
    Dim rowDataEndMDS As Long, rMDS As Range
    Dim rowHeaderMDS As Long, rowHeaderIBMass As Long
    Dim rowDataStartMDS As Long, rowDataStartIBMass As Long
    Dim headerIBMass As Range, headerMDS As Range
    
    Set wsMDS = Worksheets("MDS Equipment Detail")
    Set wsIBMass = Worksheets("IB Mass Creation")
    
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    Set headerIBMass = wsMOST.Rows(rowHeaderIBMass)
 
Last edited:
Upvote 0
What error do you get there because I don't get a Variable not defined error there?
The only error I can see happening there is if you haven't assigned a value to rowHeaderIBMass anywhere (which you haven't in the code you have posted)
 
Upvote 0
MARK858 said:
What error do you get there because I don't get a Variable not defined error there?
The only error I can see happening there is if you haven't assigned a value to rowHeaderIBMass anywhere (which you haven't in the code you have posted)
Click to expand...

Hi,

I get a Runtime Error '1004' Application-defined or Object-defined error

Below is the global code to set where the Headers and Data are located on the sheets. I have data in the Header rows on the IBMass Sheet.

Code: 
Public Const rowHeaderMDS As Long = 5       'MDS Sheet Header Start RowPublic Const rowDataStartMDS As Long = 7    'MDS Sheet Data Start Row


Public Const rowHeaderMOST As Long = 4      'MOST Sheet Header Start Row (Fixed Starting row 3/23/17)
Public Const rowDataStartMOST As Long = 5   'MOST Sheet Data Start Row  (Fixed Starting row 3/23/17)


Public Const rowHeaderPreMDS As Long = 5    'PreMDS Sheet Header Start Row
Public Const rowDataStartPreMDS As Long = 7 'PreMDS Sheet Data Start Row


Public Const rowHeaderNames As Long = 1     'Names Sheet Header Start Row
Public Const rowDataStartNames As Long = 2  'Names Sheet Data Start Row


Public Const rowHeaderIBMass As Long = 6     'IB Mass Creation Sheet Header Start Row
Public Const rowDataStartIBMass As Long = 7  'IB Mass Creation Sheet Data Start Row
 
Last edited:
Upvote 0
What is the value of rowHeaderMDS when the code errs out?
Code: 
MsgBox rowHeaderMDS
Set headerMDS = wsMDS.Rows(rowHeaderMDS)
 
Upvote 0
There is data in all headers It is all Text no Numbers. If I understand your question...
 
Upvote 0
Rows expects a number
Code: 
MsgBox rowHeaderMDS
Set headerMDS = wsMDS.[COLOR="#FF0000"]Rows[/COLOR](rowHeaderMDS)

so what does the messagebox line return?
 
Upvote 0
Hi, below is the total code, Looks like I might have copied or pasted a partial code.

The first Message Box is to return a popup if there is no data in first sheet.. The last Message Box is to return a popup once it is completed copying the data from MDS to IBMass..

The Error is: Runtime Error '1004' Application-defined or Object-defined error and it stops at Highlighted in Yellow.
Code: 
[/COLOR] Set headerMDS = wsMDS.Rows(rowHeaderMDS)



Code: 
Option Explicit

'Created on 11/27/17 BWyllie  Copy of information from MDS Equipment Detail Sheet to the IB Mass Creation Sheet


Sub BuildIBMass()
    Dim i As Long, iCol As Long, wsMDS As Worksheet
    Dim wsIBMass As Worksheet, wsMOST As Worksheet
    Dim rowDataEndMDS As Long, rMDS As Range
    Dim rowHeaderMDS As Long, rowHeaderIBMass As Long
    Dim rowDataStartMDS As Long, rowDataStartIBMass As Long
    Dim headerIBMass As Range, headerMDS As Range
    
    Set wsMDS = Worksheets("MDS Equipment Detail")
    Set wsIBMass = Worksheets("IB Mass Creation")
    
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    Set headerIBMass = wsIBMass.Rows(rowHeaderIBMass)
    
    Application.ScreenUpdating = False
    
    'get last Client Name row
    Set wsMDS = Worksheets("MDS Equipment Detail")
    Set headerMDS = wsMDS.Rows(rowHeaderMDS)
    
    'get last Client Name row
    iCol = GetColumnNumber("Client Name", headerMDS)
    
    With wsMDS
        rowDataEndMDS = .Cells(.Rows.Count, iCol).End(xlUp).row
    End With
    
    'added 11/27/2017
    If rowDataEndMDS < 7 Then
        MsgBox "No Data on MDS to copy to MOST"
        Exit Sub
    End If
    
    Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)


    Range(wsIBMass.Rows(rowDataStartIBMass), wsIBMass.Rows(rowDataEndMDS)).EntireRow.Delete
    
    'Fields to be filled in when Copy from MDS to IB Mass Creation macro is enabled
    
    For i = rowDataStartMDS To rowDataEndMDS
        With wsIBMass.Rows(i - 2)
            
            .Cells(2).Value = LUV(headerMDS, "Serial Number", i).Value
            .Cells(16).Value = LUV(headerMDS, "Ricoh Equipment ID", i).Value
            .Cells(21).Value = LUV(headerMDS, "Department Name (if required)", i).Value
            .Cells(22).Value = LUV(headerMDS, "Cost Center (if required)", i).Value
            .Cells(45).Value = LUV(headerMDS, "IP Address", i).Value
            .Cells(50).Value = LUV(headerMDS, "MAC Address", i).Value
            
            
        End With
    Next i


    Application.ScreenUpdating = True


    MsgBox "The data has been copied to the IB Mass Creation worksheet.  Please verify that the data has copied over properly!"
    


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Compile error: Variable not defined
Replies
3
Views
249
lharr28
L
K
  • Question Question
VBA Excel, Error code 91
4 5 6
Replies
56
Views
2K
Candyman8019
C
P
  • Question Question
variable List creation
Replies
0
Views
453
Pheonix2332
P
F
  • Solved
Variable Not Defined Error When Defining Variable
Replies
2
Views
639
Fretzie87
F
F
  • Question Question
VBA to copy formulas into worksheets - Application-defined error
Replies
2
Views
1K
fbohlandt
F

Forum statistics

Threads
1,223,885
Messages
6,175,180
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top