Dear Mike and Paul,
Once again thanks for the replies. Paul, I have verified that the
names are exactly the same (as they never change between the loops).
Mike, you are very right regarding that I only presented one loop but
I will now present the entire code. Bear with me, as the text with
code will be quite long.
There is another loop that changes the company to be excluded and thus
(the z and x indiate how many companies are to analyzed and where in
the sheet they are (my way of testing different set of comps to
problemsolve):
"ExcludedCompany = Sheets("Peer Group").Range("a2").Value" changes as
the A2 value changes.
The loop stops at (on the sixth company):
"Sheets(Sht).Copy Before:=Sheets(2)"
The entire code is:
"
Sub RunningThroughPGComps()
z = InputBox("What is the first row selection?", "What is the first
row selection?")
x = InputBox("What is the last row selection?", "What is the last row
selection?")
For z = z To x
Sheets("Peer Groups to go through").Select
Cells(z, 1).Copy
Worksheets("Peer Group").Select
Range("b2").Select
ActiveSheet.Paste
Condition_Industry = Range("D2").Value
Condition_CapSize = Range("E2").Value
Call CleaningPeerGroup
Next z
End Sub
Sub CleaningPeerGroup()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
'Checks whether a manual cap size is requested
size_switch = MsgBox("Do you want to make a manual cut off point
regarding MV?" & vbCrLf & "This will disregard what you chose
regarding Large/Mid Cap", vbYesNo, "Manual Cut off point regarding
MV?")
If size_switch = 6 Then
size_switch = InputBox("1 = Greater than 0 = Less than", "Do you
want to exclude greater than or less than MV?", 1, vbDefaultButton1)
Do While size_switch <> (1 Or 0)
size_switch = InputBox("1 = Greater than 0 = Less than", "Please
provide a valid number for chosing positive or negative cut off?", 1,
vbDefaultButton1)
Loop
Condition_CapSize_manual = InputBox("Cut off point MV. No further
size is checked apart from large/midcap selection", "Cut off point
regarding size")
'If a manual selection is taken the CapSize selection is
disregarded
If Not Condition_CapSize_manual = 0 Then Condition_CapSize = "All
Sizes"
End If
Condition1 = Condition_SubIndustry
Condition2 = Condition_CapSize
Condition3 = Condition_CapSize_manual
Sht = "Peer Group"
Sheets(Sht).Select
If Condition2 <> "All Sizes" Then
Range("A14").Value = "EMEA " & Condition2 & " " & Condition1
Else: Range("A14").Value = "EMEA " & Condition1
End If
'Verifies that the correct countries has been selected
Ans = MsgBox("Have you selected the countries to be studied?",
vbYesNo, "Have you selected the countries to be studied?")
If Ans = vbNo Then Exit Sub
'Defines years to analyze and company to compare
Calculate
ExcludedCompany = Sheets("Peer Group").Range("a2").Value
FirstYear = InputBox("Which is the first year you want to analyze?",
"Which is the first year you want to analyze?", 2003)
LastYear = InputBox("Which is the last year you want to analyze?",
"Which is the last year you want to analyze?", 2007)
For Year = FirstYear To LastYear
Sht2 = "" & Year & " test"
Sheets(Sht2).Delete
Sht = "" & Year & " table sheet"
Sheets(Sht).Copy Before:=Sheets(2)
Sheets(Sht & " (2)").Name = Sht2
SearchVariable = "SUBINDUSTRY"
Call Find
OffsetColumn = Cells(SearchRow, OffsetColumn).Address
NumberofComps = Sheets(Sht2).UsedRange.Rows.Count
'Isolates Peer Group in term of Industry
For i = 2 To NumberofComps
Position = Range(OffsetColumn).Offset(i - 1, 0).Address
If Not IsEmpty(Range(Position).Value) Then
If Range(Position).Value <> Condition1 Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
End If
Next i
'Eliminates the analyzed company if it is part of the peer group
Set rng = Nothing
Set rng = Cells.Find(What:=ExcludedCompany, _
After:=Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rng Is Nothing Then
Rows(rng.Row).Delete shift:=xlUp
End If
'Isolates Peer Group in terms of Capital Size
If Condition2 <> "All Sizes" Then
SearchVariable = "Large Cap / Mid-Cap Flag"
Call Find
OffsetColumn = Cells(SearchRow, OffsetColumn).Address
NumberofComps = Sheets(Sht2).UsedRange.Rows.Count
For i = 2 To NumberofComps
Position = Range(OffsetColumn).Offset(i - 1, 0).Address
If Not IsEmpty(Range(Position).Value) Then
If Range(Position).Value <> Condition2 Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
End If
Next i
End If
'Isolates Peer Group in terms of Capital Size
If Not Condition3 = 0 Then
SearchVariable = "MARKET VALUE AT END OF PERIOD"
Call Find
OffsetColumn = Cells(SearchRow, OffsetColumn).Address
NumberofComps = Sheets(Sht2).UsedRange.Rows.Count
If size_switch = 0 Then
For i = 2 To NumberofComps
Position = Range(OffsetColumn).Offset(i - 1, 0).Address
If Not IsEmpty(Range(Position).Value) Then
If (Range(Position).Value > Condition3) Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
End If
Next i
End If
If size_switch = 1 Then
For i = 2 To NumberofComps
Position = Range(OffsetColumn).Offset(i - 1, 0).Address
If Not IsEmpty(Range(Position).Value) Then
If (Range(Position).Value < Condition3) Then
Rows(i).Delete shift:=xlUp
i = i - 1
End If
End If
Next i
End If
End If
'Defines ranges and names them
EndRow = 401
SearchVariable = "MARKET VALUE AT END OF PERIOD"
Call Find
cell1 = Cells(SearchRow + 1, OffsetColumn).Address
Position = Cells(EndRow, OffsetColumn).Address
Range(cell1, Position).Name = "PG_MV_" & Year
SearchVariable = "STANDARD DEVIATION OF EXCESS RETURN"
Call Find
cell1 = Cells(SearchRow + 1, OffsetColumn).Address
Position = Cells(EndRow, OffsetColumn).Address
Range(cell1, Position).Name = "PG_Risk_" & Year
SearchVariable = "Geometric Mean"
Call Find
cell1 = Cells(SearchRow + 1, OffsetColumn).Address
Position = Cells(EndRow, OffsetColumn).Address
Range(cell1, Position).Name = "PG_ER_" & Year
SearchVariable = "SPI"
Call Find
cell1 = Cells(SearchRow + 1, OffsetColumn).Address
Position = Cells(EndRow, OffsetColumn).Address
Range(cell1, Position).Name = "PG_SPI_" & Year
Next Year
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Sheets("Peer Group").Select
End Sub
Sub Find()
Sheets(Sht2).Select
SearchRow = 1
OffsetColumn = WorksheetFunction.Match(SearchVariable, Rows(1), 0)
End Sub
"
As I said it becomes quite long. But if you have ANY suggestions as to
how to get around this it would be great. Because the macro as it is
now does everything correct for the first 5 companies. If I save and
close the file, take another five etc it all works fine. But I want to
be able to do it on 400 companies and especially over time re-run it.
And as we all know, whats the point with a macro thats not automatic?
Once again thanks for your suggestions I have received so far!
/Peder
