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