?
Solved

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

Posted on 2011-09-11
8
Medium Priority
?
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 34

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 93

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

765 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