| |
やっちんさん、コードは以下のとおりです。
Fund.xlsのDiscrepシートのA列とC列に証券名が入っています。
他のブック(Jul 2005.xlsとAug 2005xls)の複数あるシートから、対応する証券コードを探してきて、Q列とR列に証券コードを表示させるのが目的です。
Jul 2005.xlsとAug 2005.xlsのフォーマットはすべて、「A列に証券名、C列に証券コード」で統一されています。
Dim j As Integer
Dim r1(9) As Long, r2(9) As Long
Dim SheetName As Variant, ArrayCounter As Integer
SheetName = Array("CASH", "FI", "EQ")
r1(9) = Cells(Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Rows.Count, "A").End(xlUp).Row
r2(9) = Cells(Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Rows.Count, "C").End(xlUp).Row
For j = 7 To r1(9)
For k = j - 1 To j
Asset1 = Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Range("A" & k).Value
Asset2 = Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Range("C" & k).Value
For ArrayCounter = 1 To UBound(SheetName)
r1(ArrayCounter) = Cells(Workbooks(“Jul 2005.xls”).Worksheets(SheetName(ArrayCounter)).Rows.Count, "A").End(xlUp).Row
If Asset1 = "" Then
Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Range("Q" & k).Value = ""
Exit For
ElseIf Not Workbooks(“Jul 2005.xls”).Worksheets(SheetName(ArrayCounter)).Range("A3:A" & r1(ArrayCounter)).Find(Asset1) Is Nothing Then
ID1 = Workbooks(“Jul 2005.xls”).Worksheets(SheetName(ArrayCounter)).Range("A3:A" & r1(ArrayCounter)).Find(Asset1).Offset(, 2).Value
Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Range("Q" & k).Value = ID1
Exit For
End If
Next ArrayCounter
For ArrayCounter = 1 To UBound(SheetName)
r2(ArrayCounter) = Cells(Workbooks(“Aug 2005.xls”).Worksheets(SheetName(ArrayCounter)).Rows.Count, "A").End(xlUp).Row
If Asset2 = "" Then
Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Range("R" & k).Value = ""
Exit For
ElseIf Not Workbooks(“Aug 2005.xls”).Worksheets(SheetName(ArrayCounter)).Range("A3:A" & r2(ArrayCounter)).Find(Asset2) Is Nothing Then
ID2 = Workbooks(“Aug 2005.xls”).Worksheets(SheetName(ArrayCounter)).Range("A3:A" & r2(ArrayCounter)).Find(Asset2).Offset(, 2).Value
Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Range("R" & k).Value = ID2
Exit For
End If
Next ArrayCounter
Next k
If Not IsError(Cells(j, "Q")) And Not IsError(Cells(j, "R")) Then
If Cells(j, "Q").Value = Cells(j, "R").Value Then
If Cells(j, "B").Value >= 0 And Cells(j, "D") >= 0 Then
ElseIf Cells(j, "B").Value < 0 And Cells(j, "D") < 0 Then
Else
Range(Cells(j, "A"), Cells(j, "B")).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow
End If
ElseIf Cells(j, "A").Value = "Equity Total" Then
EQ2 = Application.Match("Equity Total", Range("C1:C3000"), 0)
If j < EQ2 Then
Range(Cells(j, "A"), Cells(EQ2 - 1, "B")).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow
ElseIf j > EQ2 Then
Range(Cells(EQ2, "C"), Cells(j - 1, "D")).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow
End If
End If
End If
r1(9) = Cells(Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Rows.Count, "A").End(xlUp).Row
r2(9) = Cells(Workbooks(“Fund.xls”) .Worksheets(“Discrep”).Rows.Count, "C").End(xlUp).Row
If r1(9) < r2(9) Then
r1(9) = r2(9)
End If
Next j
デバッグすると、jが95、r1(9) = r2(9)=120の時にループから抜けてしまいます。
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
