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

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
mato01Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AgeOfEmpiresConnect With a Mentor Commented:
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
 
NorieVBA ExpertCommented:
Where's the rest of the code?

Do you have a link to the original?
0
 
AgeOfEmpiresCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Patrick MatthewsCommented:
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
 
mato01Author Commented:
I've attached file, so that you can see the error that I'm receiving.
Q-27291889-TEST.xls
0
 
AgeOfEmpiresCommented:
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
 
mato01Author Commented:
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
 
mato01Author Commented:
It took me a minute to figure out what I needed to do, but thanks for the lesson.  Works now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.