Solved

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

Posted on 2011-09-11
8
308 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

13 Experts available now in Live!

Get 1:1 Help Now