Solved

Can't Modify Script - Method or Data Member Not Found

Posted on 2011-09-11
8
313 Views
Last Modified: 2012-06-27
Hi Experts,

I'm trying to convert this code for my use.  However, when I try to change the array to anything other than what's in the sample code attached.  I get the error Method or Data Member Not Found

Here are the changes which results in the error.

(Sub Export()
   
    Dim grp As clsGroup
    Dim Counter As Long
   
    With ActiveWorkbook
        .Worksheets.Add After:=.Worksheets(.Sheets.Count)
    End With
   
    [a1:m1] = Array("Change", "Allocation", "Target", "Number", "Option Code", "Description", "Pct of Qty", "Length", "Duration", "Comments", "Forecast", "Marekt", "Date Added")
    [g:g].NumberFormat = "0.00"
    [m:m].NumberFormat = "yyyy-mm-dd"
   
    For Counter = 1 To Me.Count
        Set grp = Me(Counter)
        Cells(Counter + 1, 1).Resize(1, 13) = _
            Array(grp.Change, grp.AllocationGroup, grp.TargetProduction, grp.ConstraintNumber, grp.OptionModelCode, grp.Description, grp.PctOfNatlAllocationQty, grp.Length, grp.ConstraintDuration, grp.Comments, grp.Forecaster, grp.Marketing, grp.DateAdded)
           
    Next
   
    Columns.AutoFit
   
    On Error Resume Next
    ActiveSheet.Name = "Results"
    On Error GoTo 0
   
    Set grp = Nothing
   
End Sub

Sub Import()
   
    Dim ws As Worksheet
    Dim LastR As Long
    Dim arr As Variant
    Dim Counter As Long
    Dim TestChange As String
    Dim TestDate As Date
    Dim grp As clsGroup
   
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .[a1] = "Change" Then
                LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
                arr = .Range("a1:m" & LastR).Value
                For Counter = 2 To LastR
                    TestChange = arr(Counter, 1)
                    TestDate = arr(Counter, 13)
                    If Me.Exists(TestChange) Then
                        Set grp = Me(TestChange)
                    Else
                        Set grp = Me.Add(TestChange)
                    End If
                    If TestDate > grp.DateAdded Then
                        grp.AllocationGroup = arr(Counter, 2)
                        grp.TargetProduction = arr(Counter, 3)
                        grp.ConstraintNumber = arr(Counter, 4)
                        grp.OptionModelCode = arr(Counter, 5)
                        grp.Description = arr(Counter, 6)
                        grp.PctOfNatlAllocationQty = arr(Counter, 7)
                        grp.Length = arr(Counter, 8)
                        grp.ConstraintDuration = arr(Counter, 9)
                        grp.Comments = arr(Counter, 10)
                        grp.Forecaster = arr(Counter, 11)
                        grp.Marketing = arr(Counter, 12)
                        grp.DateAdded = TestDate
                    End If
                Next
            End If
        End With
    Next
   
    Set grp = Nothing
   
End Sub
0
Comment
Question by:mato01
8 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 36519125
Where's the rest of the code?

Do you have a link to the original?
0
 
LVL 4

Expert Comment

by:AgeOfEmpires
ID: 36519142
What does clsGroup look like and which array are you referring to that you are changing?  clsGroup is a class defined in your code somewhere that contains properties and methods.  The error you are getting indicates, to me, that you are referencing a non-existent property or method of an instance of clsGroup (via grp).

Look at your array modifications and cross reference to the clsGroup definition to make sure this isn't the case.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36519381
I think AgeOfEmpires has this one nailed.

When you took the example I provided in http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27291889.html, did you go back and modify the code in the class module clsGroup to include all of the properties that you are using here?

It would be helpful if you posted a sample file that included the code you are trying to run, as well as a sufficient data sample to troubleshoot.  The data can be "fake" or obfuscated as needed--do not post confidential or proprietary data.
0
 

Author Comment

by:mato01
ID: 36522756
I've attached file, so that you can see the error that I'm receiving.
Q-27291889-TEST.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:AgeOfEmpires
ID: 36523632
For example, in your Export routine in the clsGroups module, you are defining an instance of clsGroup (referred to by grp) and referring to non-existent properties/methods such as TargetProduction,Change, and ConstraintNumber.  These should have a definition in the clsGroup class module, yet I do not see them.  That module seems to be pretty sparse.
0
 

Author Comment

by:mato01
ID: 36524367
I updated the instances in the Export routine.  See code below.
Sub Export()
    
    Dim grp As clsGroup
    Dim Counter As Long
    
    With ActiveWorkbook
        .Worksheets.Add After:=.Worksheets(.Sheets.Count)
    End With
    
    [a1:m1] = Array("Change", "Allocation Group", "Target Production", "Constraint Number", "Option Model Code", "Description", "Pct Of Natl Allocation Qty", "Length", "Constraint Duration", "Comments", "Forecaster", "Marketing", "Date Added")
    [g:g].NumberFormat = "0.00"
    [m:m].NumberFormat = "yyyy-mm-dd"
    
    For Counter = 1 To Me.Count
        Set grp = Me(Counter)
        Cells(Counter + 1, 1).Resize(1, 13) = _
            Array(grp.Change, grp.AllocationGroup, grp.TargetProduction, grp.ConstraintNumber, grp.OptionModelCode, grp.Description, grp.PctOfNatlAllocationQty, grp.Length, grp.ConstraintDuration, grp.Comments, grp.Forecaster, grp.Marketing, grp.DateAdded)
            
    Next
    
    Columns.AutoFit
    
    On Error Resume Next
    ActiveSheet.Name = "Results"
    On Error GoTo 0
    
    Set grp = Nothing
    
End Sub

Open in new window

0
 
LVL 4

Accepted Solution

by:
AgeOfEmpires earned 500 total points
ID: 36524614
mato01

I think you missed the point of my last post - sorry if I wasn't clear enough.

You are using an instance of clsGroup indicated by the variable grp in your code, the Export routine was simply an example.  I didn't really examine your logic, so I can't say if there is a logic problem in Export or elsewhere in your code.  

My point is that you are creating an instance (grp) of clsGroup and then referring to properties/methods that VBA expects to be defined in the class module for clsGroup (like .Change, .AllocationGroup, etc), yet in the sample code that I looked at, the clsGroup module did not contain any definitions for these.  The lack of these will generate the error you are getting, "Method or Data Member not found".  When you say "grp.AllocationGroup", VBA goes to the definition of the clsGroup (since that is what grp is) and looks for either a procedure (method) or property (Data Member) defined for that class called "AllocationGroup", and it is not finding such definition.

Hopefully this helps clarify.
0
 

Author Closing Comment

by:mato01
ID: 36538448
It took me a minute to figure out what I needed to do, but thanks for the lesson.  Works now.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql server Transfering records getting error #NAME? 6 39
Excel ODBC connection error 3 31
Remove row and column 3 45
Update As Well As Add 6 34
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now