2007 macros (or earlier) not working with 2013

Hiker

New Member
Joined
Jun 15, 2011
Messages
24
I'm practically illiterate with macros but took over a membership workbook with ~2000 members. Various macros distinguish members geologically, alphabetically, nicknames, etc. and makes various lists with Word (not all the macros do this).

Most are stopping with run-time error: 13 type mismatch (is this case in yellow text near end of code)

Here's an example:

Code:
Sub GeogDist()
    
    ' Creates geographic distibution of members and stores counts in
    ' States worksheet.


    ' Define working sheets and their relevant columns.


    Dim MemberSheet
    MemberSheet = "Members"
    Dim MemNo
    MemNo = "MemNo"
    Dim StateCol
    StateCol = "state"


    Dim StatesSheet
    StatesSheet = "States"
    Dim StAbb
    StateAbbrev = "StAbb"
    Dim CountCol
    CountCol = "Count"


    Dim StateArr(63, 1)


    Sheets(StatesSheet).Select


    ' Determine the last row and column of the worksheet
    lr = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    lc = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    
    ' Determine the StateAbbreviations  column
    For i = 1 To lc
        If Worksheets(StatesSheet).Cells(1, i).Value = StateAbbrev Then
            sa = i
            Exit For
        End If
    Next i
    
    Cells(2, sa).Select
    
    ' Create array of state abbreviations
    For i = 0 To 63
        StateArr(i, 0) = ActiveCell.Value
        ActiveCell.Offset(1, 0).Select ' Move down one row
    Next


    ' Determine the member count column
    For j = 1 To lc
        If Worksheets(StatesSheet).Cells(1, j).Value = CountCol Then
            uc = j
            Exit For
        End If
    Next j




    Sheets(MemberSheet).Select


    Dim strStartCell As String
    Dim strEndCell As String
    Dim strFullColumn As String




    ' Determine the last row and column of the worksheet
    lr = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    lc = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column


    ' Determine the number of members column
    For i = 1 To lc
        If Worksheets(MemberSheet).Cells(1, i).Value = MemNo Then
            mn = i
            Exit For
        End If
    Next i
    
    ' Determine the states column
    For i = 1 To lc
        If Worksheets(MemberSheet).Cells(1, i).Value = StateCol Then
            sc = i
            Exit For
        End If
    Next i
    
    ofst = sc - mn ' create the column offset differential btwn memno and state


    Dim rng As Range




     ' For each member record, add the member count to the state total
    For Each rng In ActiveSheet.Range(Cells(2, mn), Cells(lr, mn))
        State = rng.Offset(0, ofst).Value
        Members = rng.Value
        For i = 0 To 63
            If StateArr(i, 0) = State Then
[COLOR=#ffff00][B]                StateArr(i, 1) = StateArr(i, 1) + Members[/B][/COLOR]
            End If
        Next
    Next




    ' Write member counts to the States spreadsheet
    Sheets(StatesSheet).Select
    Range(Cells(2, uc), Cells(65, uc)).Clear ' Remove previous counts
    Cells(2, uc).Select
    
    For j = 0 To 63
        ActiveCell.Value = StateArr(j, 1)
        ActiveCell.Offset(1, 0).Select ' Move down one row
    Next


End Sub
 
Last edited:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

timfritsch

Active Member
Joined
Jun 10, 2004
Messages
343
Shot in the dark: change Dim StateArr(63, 1) to Dim StateArr(0 to 63,1). Rerun the report and see if you get the error in the same spot.


Tim
 

Hiker

New Member
Joined
Jun 15, 2011
Messages
24
are you saying that the macro works with earlier versions??

Yes it did. My laptop with Office 2010 crashed and Microsoft gave me the option of installing Office 2013 on the replacement. I restored the files from a Mozy back-up and the files opened fine, but none of the macros worked.

As far as I know the macros may have been written using 2003 or 2007 software.
 
Last edited:

Hiker

New Member
Joined
Jun 15, 2011
Messages
24

ADVERTISEMENT

Shot in the dark: change Dim StateArr(63, 1) to Dim StateArr(0 to 63,1). Rerun the report and see if you get the error in the same spot.


Tim

Yes error is the same.
 

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Yes it did. My laptop with Office 2010 crashe.....

As far as I know the macros may have been written using 2003 or 2007 software.


Hi Hiker,
.. I did have some issues with conflicting Library references with Functions going from 2010 / 2013 back to 2007. They were overcome mostly by being a bit more explicit and referencing with an extra
VBA. ( so for example instead of

Code:
=Left(_____,_____)
One would write
Code:
=VBA.Left(____,____)

…)
. This more explicit version ensures referencing was going , as it were to the “Old / standard “ Library rather than a new one that was added with a newer version of Excel and which somehow seemed to confuse and give strange errors.. IF and only if the program had been written on the earlier versions.
. At first glance the Function referencing does not appear a problem here

. Another option in a similar fashion though to the solution I had above would be to re-write the code in a 2013 module ( not literally, you could paste parts of the code to a text file, then paste to a fresh module ). Some times peculiar problem of this nature have been solved by me that way. Note then however you may have a reverse problem trying to run that code in earlier versions.

. Other than that I cannot help further – without the relevant data, your code, of course will not run by me and crashes everywhere so debugging is impossible.
. - I assume you are sure that it is a 2013 problem . – you still have somewhere access to earlier versions of excel to confirm he programs still work correctly.??
. If not, and you had a simpler program that required little or no test data which you could supply, then I could run that here later (I have 2003, 2007, and 2010 ) and confirm for you, at least, that on earlier versions the code was still working

. Sorry I cannot be more help. Someone would need access to XL 2013 and test data to run / help with debugging your code further.

Regards
Mustava Klatsche
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,520
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

The most likely cause of the error would be if the Member variable contains an error or non-numeric value. What is its value when the error occurs? (If you click debug when the error happens, then hover the cursor over the word Member in the code, you should see a tooltip with its value in.)
 

Hiker

New Member
Joined
Jun 15, 2011
Messages
24
Thanks for your help gentlemen I'll see id I can narrow down the problem and ask the old memsec if the macros work on her machine.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,406
Messages
5,528,582
Members
409,827
Latest member
Tmcgrew05

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top