I have copied my code below. But to create the add-in, I opened a new
workbook, opened VBA, copied the code into a new module. Went back to the
workbook, gave it a tile in the properties under file. Tested the code on
this workbook and then saved it as an *.xla file. I have had two things
happen:

1)either when I select the add-in the workbook I am looking at will freeze
and I won't be able to select any cells, although I can do everything else,
ie use the file menus, look at vba etc.

2) when I have gotten the add-in to seem to install correctly and it shows
up in vba but not as a workbook, the macro does not show up in the list of
macros on any workbook.

Can someone help? Thanks


'Programmer: Tscharner Upjohn
'Date: 2/20/07
'Purpose: Copy average capital from an excel sheet 1 brought in from a
performance report in advent
' to sheet 2 matching the account codes and putting average capital values
next to them

Public Sub getAvgCapital()
'Declaration
Dim acctIndex1, acctIndex2, avgCapIndex, firstARow, firstKRow,
firstSRow, blanks As Integer
Dim acct As String
Dim endFile As Boolean

'Initialization
acctIndex1 = 1
blanks = 0
endFile = False
acct = ActiveWindow.RangeSelection.Cells(1, 1).Value

'Count number of rows in sheet1 finding the first account that starts
with a (firstARow)
'k (firstKRow) and with s (firstSRow)
Do While firstSRow = 0
numRows1 = numRows1 + 1
temp = Workbooks("iccallAvgCap.xls").Worksheets("sheet1") .Range("A"
& numRows1).Value
If Len(temp) = 8 Then
Select Case Left(temp, 1)
Case "a", "A"
If (firstARow = 0) Then
firstARow = numRows1
End If
Case "k", "K"
If (firstKRow = 0) Then
firstKRow = numRows1
End If
Case "s", "S"
If (firstSRow = 0) Then
firstSRow = numRows1
End If
End Select
End If
Loop
Do While acct < ""
Select Case Left(acct, 1)
Case 0 To 9
acctIndex2 = 1
Case "a" To "j", "A" To "J"
acctIndex2 = firstARow
Case "k" To "r", "K" To "R"
acctIndex2 = firstKRow
Case Else
acctIndex2 = firstSRow
End Select
Do While endFile < True
If (Workbooks("iccallAvgCap.xls").Worksheets("sheet1" ).Range("A"
& acctIndex2).Value = acct) Then
avgCapIndex = acctIndex2 + 1
Do While
Workbooks("iccallAvgCap.xls").Worksheets("sheet1") .Range("A" &
avgCapIndex).Value < "Average Capital"
avgCapIndex = avgCapIndex + 1
Loop
ActiveWindow.RangeSelection.Cells(acctIndex1, 2).Value =
Workbooks("iccallAvgCap.xls").Worksheets("sheet1") .Range("B" &
avgCapIndex).Value
endFile = True
End If
acctIndex2 = acctIndex2 + 1
Loop

acctIndex1 = acctIndex1 + 1
acct = ActiveWindow.RangeSelection.Cells(acctIndex1, 1).Value
endFile = False
Loop
End Sub

macros in an addin do not show up in the Tools=Macro=Macros list. this is
expected behavior.

--
Regards,
Tom Ogilvy


"icccapital" wrote:

I have copied my code below. But to create the add-in, I opened a new
workbook, opened VBA, copied the code into a new module. Went back to the
workbook, gave it a tile in the properties under file. Tested the code on
this workbook and then saved it as an *.xla file. I have had two things
happen:

1)either when I select the add-in the workbook I am looking at will freeze
and I won't be able to select any cells, although I can do everything else,
ie use the file menus, look at vba etc.

2) when I have gotten the add-in to seem to install correctly and it shows
up in vba but not as a workbook, the macro does not show up in the list of
macros on any workbook.

Can someone help? Thanks


'Programmer: Tscharner Upjohn
'Date: 2/20/07
'Purpose: Copy average capital from an excel sheet 1 brought in from a
performance report in advent
' to sheet 2 matching the account codes and putting average capital values
next to them

Public Sub getAvgCapital()
'Declaration
Dim acctIndex1, acctIndex2, avgCapIndex, firstARow, firstKRow,
firstSRow, blanks As Integer
Dim acct As String
Dim endFile As Boolean

'Initialization
acctIndex1 = 1
blanks = 0
endFile = False
acct = ActiveWindow.RangeSelection.Cells(1, 1).Value

'Count number of rows in sheet1 finding the first account that starts
with a (firstARow)
'k (firstKRow) and with s (firstSRow)
Do While firstSRow = 0
numRows1 = numRows1 + 1
temp = Workbooks("iccallAvgCap.xls").Worksheets("sheet1") .Range("A"
& numRows1).Value
If Len(temp) = 8 Then
Select Case Left(temp, 1)
Case "a", "A"
If (firstARow = 0) Then
firstARow = numRows1
End If
Case "k", "K"
If (firstKRow = 0) Then
firstKRow = numRows1
End If
Case "s", "S"
If (firstSRow = 0) Then
firstSRow = numRows1
End If
End Select
End If
Loop
Do While acct < ""
Select Case Left(acct, 1)
Case 0 To 9
acctIndex2 = 1
Case "a" To "j", "A" To "J"
acctIndex2 = firstARow
Case "k" To "r", "K" To "R"
acctIndex2 = firstKRow
Case Else
acctIndex2 = firstSRow
End Select
Do While endFile < True
If (Workbooks("iccallAvgCap.xls").Worksheets("sheet1" ).Range("A"
& acctIndex2).Value = acct) Then
avgCapIndex = acctIndex2 + 1
Do While
Workbooks("iccallAvgCap.xls").Worksheets("sheet1") .Range("A" &
avgCapIndex).Value < "Average Capital"
avgCapIndex = avgCapIndex + 1
Loop
ActiveWindow.RangeSelection.Cells(acctIndex1, 2).Value =
Workbooks("iccallAvgCap.xls").Worksheets("sheet1") .Range("B" &
avgCapIndex).Value
endFile = True
End If
acctIndex2 = acctIndex2 + 1
Loop

acctIndex1 = acctIndex1 + 1
acct = ActiveWindow.RangeSelection.Cells(acctIndex1, 1).Value
endFile = False
Loop
End Sub