Solved

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

Posted on 2011-09-11
8
329 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

789 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