Saya telah membuat VBA (seperti terlampir di email ini.)
Tapi rasanya lambat dalam memproses data.
bila mengunakan do case atau select case apakah akan lebih cepat ?
bagaimana cara merubahnya . bila hendak mengunakan do case. /select case.
Salam
Lukman
NB :
1. Komplit_1 -- ini lambat
2. Komplit_2 -- ini lebih cepat daripada yang diatas.
Sub Isi_data_LK_100()
Dim Wlk As Workbook
Dim Slk1 As Worksheet
Dim Rlk As Long, Clk As Long
Dim Vlk As Range, Hlk As Range, Dtlk As Range, HqtK As Range
Dim rc As Long, cc As Long, xc As Long
'Variable untuk membantu
Dim Kurs As Long, Tic As String, acc As String, matauang As String
Dim qtr As String, qtrQ As String, qtrY As String, qtrA As String
Dim qtk As String, qtkQ As String, qtkY As String, qtkA As String
Dim Ban1 As Variant, Ban2 As Variant, Ban3 As Variant, ban4 As Variant
Dim cmd As Variant
Set Wlk = Workbooks("LK_Induk_bikin_formula.xlsm")
Set Slk1 = Workbooks("LK_Induk_bikin_formula.xlsm").Worksheets("Lkdt")
Clk = Slk1.Cells(1, Columns.Count).End(xlToLeft).Column
Rlk = Slk1.Cells(Rows.Count, 1).End(xlUp).Row
Set Vlk = Slk1.Range(Cells(1, 4), Cells(Rlk, 4))
Set Hlk = Slk1.Range(Cells(1, 1), Cells(1, Clk))
Set Dtlk = Slk1.Range(Cells(1, 1), Cells(Rlk, Clk))
Application.Calculation = xlCalculationManual
On Error Resume Next
With Application.WorksheetFunction
Cells(2, 10).Clear
Cells(3, 10).Clear
Cells(4, 10).Clear
cmd = Timer
Cells(2, 10) = Time
cc = 47
'For cc = 46 To 16
For xc = 4 To 34
cc = cc - 1
'Quater umum
qtr = Cells(1, cc)
qtrY = Left(qtr, 4) - 1 & Mid(qtr, 5, 3)
If Right(qtr, 2) = "Q1" Then
qtrQ = Left(qtr, 4) - 1 & " Q4"
Else
qtrQ = Left(qtr, 6) & Right(qtr, 1) - 1
End If
qtrY = Left(qtr, 4) - 1 & Mid(qtr, 5, 3)
If Right(qtr, 2) <> "Q4" Then
qtrA = Left(qtr, 4) - 1 & " Q4"
End If
For rc = 5 To Rlk
'For rc = 5 To 300
acc = Cells(rc, 3)
'Quater khusus
If acc = "aa0" Then
qtk = Cells(rc, cc)
qtkY = Left(qtk, 4) - 1 & Mid(qtk, 5, 3)
Set HqtK = Slk1.Range(Cells(rc, 1), Cells(rc, Clk))
If Right(qtk, 2) = "Q1" Then
qtkQ = Left(qtk, 4) - 1 & " Q4"
Else
qtkQ = Left(qtk, 6) & Right(qtk, 1) - 1
End If
If Right(qtk, 1) <> 4 Then
qtkA = Left(qtk, 4) - 1 & " Q4"
End If
End If
'aa1= Kurs
If acc = "aa1=" Then
Tic = Cells(rc, 1)
If Cells(rc, cc) = "(USD0" Then
matauang = "(USD)"
Kurs = Cells(3, cc)
ElseIf Cells(rc, cc) = "(IDR)" Then
matauang = "(IDR)"
Kurs = 1
End If
End If
If Cells(rc, 1) = Tic And Len(matauang) > 1 Then
If acc = "AT110" Then 'AT110 Periksa balance neraca
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa2=", Vlk,
False), cc) - .Index(Dtlk, .Match(Tic & "aa3=", Vlk, False), cc) _
- .Index(Dtlk, .Match(Tic & "aa6=", Vlk,
False), cc) - .Index(Dtlk, .Match(Tic & "aa7=", Vlk, False), cc)
End If
If acc = "AT120" Then 'AT120 Periksa laba sebelum pajak
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae5=", Vlk,
False), cc) + .Index(Dtlk, .Match(Tic & "ae6=", Vlk, False), cc) _
+ .Index(Dtlk, .Match(Tic & "ae7=", Vlk,
False), cc) - .Index(Dtlk, .Match(Tic & "ae9=", Vlk, False), cc)
End If
If acc = "AT130" Then 'AT 130 Periksa laba akhir
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae9=", Vlk,
False), cc) + .Index(Dtlk, .Match(Tic & "ag2=", Vlk, False), cc) _
- .Index(Dtlk, .Match(Tic & "ag5=", Vlk,
False), cc) - .Index(Dtlk, .Match(Tic & "ag6=", Vlk, False), cc)
End If
If acc = "AT140" Then 'AT 140 Periksa nilai nominal
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa4=", Vlk,
False), cc) / .Index(Dtlk, .Match(Tic & "aa8=", Vlk, False), cc)
End If
If acc = "ba2=" Then 'ba2= Aktiva
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa2=", Vlk,
False), cc) * Kurs
End If
If acc = "ba3=" Then 'ba3= Kewajiban
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa3=", Vlk,
False), cc) * Kurs
End If
If acc = "ba6=" Then 'ba6= Ekuitas
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa6=", Vlk,
False), cc) * Kurs
End If
If acc = "be1=" Then 'be1= Pendapatan
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae1=", Vlk,
False), cc) * Kurs
End If
If acc = "be3=" Then 'be3= Laba kotor
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae3=", Vlk,
False), cc) * Kurs
End If
If acc = "be5=" Then 'be5= Laba usaha
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae5=", Vlk,
False), cc) * Kurs
End If
If acc = "bg5=" Then 'bg5= Laba bersih
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ag5=", Vlk,
False), cc) * Kurs
End If
If acc = "BB202" Then 'BS202 Jumlah saham setelah di beli
kembali
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa8=", Vlk,
False), cc) + .Index(Dtlk, .Match(Tic & "ab8.80", Vlk, False), cc)
End If
If acc = "CC410" Then 'CC410 Kapitalisasi saham
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "AT100", Vlk,
False), cc) * .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
End If
If acc = "DD312" Then 'DD312 Persentase laba kotor berbanding
pendapatan
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "DD313" Then 'DD313 Persentase laba usaha berbanding
pendapatan
Ban1 = .Index(Dtlk, .Match(Tic & "bE5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "DD314" Then 'DD314 Persentase laba bersih
berbanding pendapatan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "DD310" Then 'DD310 DER
Ban1 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE110" Then 'EE110 Book value
Ban1 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "GG206" Then 'GG206 Book per share
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE110", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If Len(qtk) = 0 Then
If acc = "CC220" Then 'CC220 Laba bersih 12 bulan terakhir
If Right(qtr, 2) = "Q4" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "bg5=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q4" Then
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtrA, Hlk, False))
Ban3 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = Ban1 + Ban2 - Ban3
End If
End If
End If
If acc = "CC420" Then 'CC420 Laba bersih disetahunkan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban1 = Ban1 * 4 / Right(qtr, 1)
Cells(rc, cc).Value = Ban1
End If
If acc = "CC301" Then 'CC301 Pendapatan per quater
If Right(qtr, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be1=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
End If
If acc = "CC302" Then 'CC302 Laba kotor per quater
If Right(qtr, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be3=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
End If
If acc = "CC303" Then 'CC303 Laba usaha per quater
If Right(qtr, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be5=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
End If
If acc = "CC304" Then 'CC303 Laba bersih per quater
If Right(qtr, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "bg5=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
End If
If acc = "DD211" Then 'DD211 Persentase pendapatan dibanding
quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC301", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC301", Vlk, False),
.Match(qtrQ, Hlk, False))
Ban3 = .Match(qtrQ, Hlk, False)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD212" Then 'DD212 Persentase laba kotor dibanding
quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC302", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC302", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD213" Then 'DD213 Persentase laba usaha dibanding
quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD214" Then 'DD214 Persentase laba bersih dibanding
quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD101" Then 'DD101 Persentase pendapatan dibanding
tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD102" Then 'DD102 Persentase laba kotor dibanding
tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD103" Then 'DD103 Persentase laba usaha dibanding
tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD104" Then 'DD104 Persentase laba bersih dibanding
tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD317" Then 'DD317 Return of equity
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "DD318" Then 'DD318 Return of aset
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba2=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "DD910" Then 'DD910 Return of equity per quater
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False), cc)
Ban1 = Ban1 * 4
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "EE121" Then 'EE121 EPS berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE122" Then 'EE122 EPS berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE123" Then 'EE123 EPS disetahunkan
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE124" Then 'EE124 EPS dalam 12 bulan terakhir
Ban1 = .Index(Dtlk, .Match(Tic & "CC220", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE310" Then 'EE310 Kapitalisasi saham berbanding
laba usaha (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False), cc)
Ban2 = Ban2 * 4 / Right(qtr, 1)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE403" Then 'EE403 (Kapitalisasi saham + pasiva)
berbanding laba usaha (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban3 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False), cc)
Ban3 = Ban3 * 4 / Right(qtr, 1)
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = (Ban1 + Ban2) / Abs(Ban3)
End If
End If
If acc = "EE404" Then 'EE404 (Kapitalisasi saham + pasiva)
berbanding laba usaha per quater (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban3 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False), cc)
Ban3 = Ban3 * 4
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = (Ban1 + Ban2) / Abs(Ban3)
End If
End If
If acc = "GG201" Then 'GG201 Per berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE123", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "GG310" Then 'GG310 Per Per quater
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE122", Vlk, False), cc)
Ban2 = Ban2 * 4
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "HH101" Then 'HH101 PEG
Ban1 = .Index(Dtlk, .Match(Tic & "GG201", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "DD104", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
ElseIf Len(qtk) > 0 Then
If acc = "CC220" Then 'CC220 Laba bersih 12 bulan terakhir
If Right(qtk, 2) = "Q4" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "bg5=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q4" Then
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtkA, HqtK, False))
Ban3 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = Ban1 + Ban2 - Ban3
End If
End If
End If
If acc = "CC420" Then 'CC420 Laba bersih disetahunkan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban1 = Ban1 * 4 / Right(qtk, 1)
Cells(rc, cc).Value = Ban1
End If
If acc = "CC301" Then 'CC301 Pendapatan per quater
If Right(qtk, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be1=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
End If
If acc = "CC302" Then 'CC302 Laba kotor per quater
If Right(qtk, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be3=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
End If
If acc = "CC303" Then 'CC303 Laba usaha per quater
If Right(qtk, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be5=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
End If
If acc = "CC304" Then 'CC303 Laba bersih per quater
If Right(qtk, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "bg5=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
End If
If acc = "DD211" Then 'DD211 Persentase pendapatan dibanding
quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC301", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC301", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD212" Then 'DD212 Persentase laba kotor dibanding
quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC302", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC302", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD213" Then 'DD213 Persentase laba usaha dibanding
quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD214" Then 'DD214 Persentase laba bersih dibanding
quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD101" Then 'DD101 Persentase pendapatan dibanding
tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD102" Then 'DD102 Persentase laba kotor dibanding
tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD103" Then 'DD103 Persentase laba usaha dibanding
tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD104" Then 'DD104 Persentase laba bersih dibanding
tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
End If
If acc = "DD317" Then 'DD317 Return of equity
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "DD318" Then 'DD318 Return of aset
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba2=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "DD910" Then 'DD910 Return of equity per quater
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False), cc)
Ban1 = Ban1 * 4
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
End If
If acc = "EE121" Then 'EE121 EPS berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE122" Then 'EE122 EPS berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE123" Then 'EE123 EPS disetahunkan
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE124" Then 'EE124 EPS dalam 12 bulan terakhir
Ban1 = .Index(Dtlk, .Match(Tic & "CC220", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE310" Then 'EE310 Kapitalisasi saham berbanding
laba usaha (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False), cc)
Ban2 = Ban2 * 4 / Right(qtk, 1)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "EE403" Then 'EE403 (Kapitalisasi saham + pasiva)
berbanding laba usaha (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban3 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False), cc)
Ban3 = Ban3 * 4 / Right(qtk, 1)
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = (Ban1 + Ban2) / Abs(Ban3)
End If
End If
If acc = "EE404" Then 'EE404 (Kapitalisasi saham + pasiva)
berbanding laba usaha per quater (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban3 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False), cc)
Ban3 = Ban3 * 4
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = (Ban1 + Ban2) / Abs(Ban3)
End If
End If
If acc = "GG201" Then 'GG201 Per berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE123", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "GG310" Then 'GG310 Per Per quater
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE122", Vlk, False), cc)
Ban2 = Ban2 * 4
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If acc = "HH101" Then 'HH101 PEG
Ban1 = .Index(Dtlk, .Match(Tic & "GG201", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "DD104", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
End If
Ban1 = 0
Ban2 = 0
Ban3 = 0
ban4 = 0
If Cells(rc, 1) <> Cells(rc + 1, 1) Then
Tic = ""
matauang = ""
qtk = ""
End If
End If
Next rc
Next xc
End With
cmd = Timer - cmd
Cells(3, 10).Value = cmd
Cells(4, 10).Value = Time
End Sub
Sub Isi_data_LK_200()
Dim Wlk As Workbook
Dim Slk1 As Worksheet
Dim Rlk As Long, Clk As Long
Dim Vlk As Range, Hlk As Range, Dtlk As Range, HqtK As Range
Dim rc As Long, cc As Long, xc As Long
'Variable untuk membantu
Dim Kurs As Long, Tic As String, acc As String, matauang As String
Dim qtr As String, qtrQ As String, qtrY As String, qtrA As String
Dim qtk As String, qtkQ As String, qtkY As String, qtkA As String
Dim Ban1 As Variant, Ban2 As Variant, Ban3 As Variant, ban4 As Variant
Dim cmd As Variant
Set Wlk = Workbooks("LK_Induk_bikin_formula.xlsm")
Set Slk1 = Workbooks("LK_Induk_bikin_formula.xlsm").Worksheets("Lkdt")
Clk = Slk1.Cells(1, Columns.Count).End(xlToLeft).Column
Rlk = Slk1.Cells(Rows.Count, 1).End(xlUp).Row
Set Vlk = Slk1.Range(Cells(1, 4), Cells(Rlk, 4))
Set Hlk = Slk1.Range(Cells(1, 1), Cells(1, Clk))
Set Dtlk = Slk1.Range(Cells(1, 1), Cells(Rlk, Clk))
Application.Calculation = xlCalculationManual
On Error Resume Next
With Application.WorksheetFunction
Cells(2, 10).Clear
Cells(3, 10).Clear
Cells(4, 10).Clear
cmd = Timer
Cells(2, 10) = Time
cc = 47
'For cc = 46 To 16
For xc = 4 To 34
cc = cc - 1
'Quater umum
qtr = Cells(1, cc)
qtrY = Left(qtr, 4) - 1 & Mid(qtr, 5, 3)
If Right(qtr, 2) = "Q1" Then
qtrQ = Left(qtr, 4) - 1 & " Q4"
Else
qtrQ = Left(qtr, 6) & Right(qtr, 1) - 1
End If
qtrY = Left(qtr, 4) - 1 & Mid(qtr, 5, 3)
If Right(qtr, 2) <> "Q4" Then
qtrA = Left(qtr, 4) - 1 & " Q4"
End If
For rc = 5 To Rlk
'For rc = 5 To 300
acc = Cells(rc, 3)
'Quater khusus
If acc = "aa0" Then
qtk = Cells(rc, cc)
qtkY = Left(qtk, 4) - 1 & Mid(qtk, 5, 3)
Set HqtK = Slk1.Range(Cells(rc, 1), Cells(rc, Clk))
If Right(qtk, 2) = "Q1" Then
qtkQ = Left(qtk, 4) - 1 & " Q4"
Else
qtkQ = Left(qtk, 6) & Right(qtk, 1) - 1
End If
If Right(qtk, 1) <> 4 Then
qtkA = Left(qtk, 4) - 1 & " Q4"
End If
End If
'aa1= Kurs
If acc = "aa1=" Then
Tic = Cells(rc, 1)
If Cells(rc, cc) = "(USD0" Then
matauang = "(USD)"
Kurs = Cells(3, cc)
ElseIf Cells(rc, cc) = "(IDR)" Then
matauang = "(IDR)"
Kurs = 1
End If
End If
If Cells(rc, 1) = Tic And Len(matauang) > 1 Then
If acc = "AT110" Then 'AT110 Periksa balance neraca
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa2=", Vlk,
False), cc) - .Index(Dtlk, .Match(Tic & "aa3=", Vlk, False), cc) _
- .Index(Dtlk, .Match(Tic & "aa6=", Vlk,
False), cc) - .Index(Dtlk, .Match(Tic & "aa7=", Vlk, False), cc)
ElseIf acc = "AT120" Then 'AT120 Periksa laba sebelum pajak
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae5=", Vlk,
False), cc) + .Index(Dtlk, .Match(Tic & "ae6=", Vlk, False), cc) _
+ .Index(Dtlk, .Match(Tic & "ae7=", Vlk,
False), cc) - .Index(Dtlk, .Match(Tic & "ae9=", Vlk, False), cc)
ElseIf acc = "AT130" Then 'AT 130 Periksa laba akhir
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae9=", Vlk,
False), cc) + .Index(Dtlk, .Match(Tic & "ag2=", Vlk, False), cc) _
- .Index(Dtlk, .Match(Tic & "ag5=", Vlk,
False), cc) - .Index(Dtlk, .Match(Tic & "ag6=", Vlk, False), cc)
ElseIf acc = "AT140" Then 'AT 140 Periksa nilai nominal
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa4=", Vlk,
False), cc) / .Index(Dtlk, .Match(Tic & "aa8=", Vlk, False), cc)
ElseIf acc = "ba2=" Then 'ba2= Aktiva
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa2=", Vlk,
False), cc) * Kurs
ElseIf acc = "ba3=" Then 'ba3= Kewajiban
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa3=", Vlk,
False), cc) * Kurs
ElseIf acc = "ba6=" Then 'ba6= Ekuitas
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa6=", Vlk,
False), cc) * Kurs
ElseIf acc = "be1=" Then 'be1= Pendapatan
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae1=", Vlk,
False), cc) * Kurs
ElseIf acc = "be3=" Then 'be3= Laba kotor
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae3=", Vlk,
False), cc) * Kurs
ElseIf acc = "be5=" Then 'be5= Laba usaha
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ae5=", Vlk,
False), cc) * Kurs
ElseIf acc = "bg5=" Then 'bg5= Laba bersih
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "ag5=", Vlk,
False), cc) * Kurs
ElseIf acc = "BB202" Then 'BS202 Jumlah saham setelah di beli
kembali
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "aa8=", Vlk,
False), cc) + .Index(Dtlk, .Match(Tic & "ab8.80", Vlk, False), cc)
ElseIf acc = "CC410" Then 'CC410 Kapitalisasi saham
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "AT100", Vlk,
False), cc) * .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
ElseIf acc = "DD312" Then 'DD312 Persentase laba kotor
berbanding pendapatan
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "DD313" Then 'DD313 Persentase laba usaha
berbanding pendapatan
Ban1 = .Index(Dtlk, .Match(Tic & "bE5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "DD314" Then 'DD314 Persentase laba bersih
berbanding pendapatan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "DD310" Then 'DD310 DER
Ban1 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE110" Then 'EE110 Book value
Ban1 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "GG206" Then 'GG206 Book per share
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE110", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
If Len(qtk) = 0 Then
If acc = "CC220" Then 'CC220 Laba bersih 12 bulan terakhir
If Right(qtr, 2) = "Q4" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "bg5=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q4" Then
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtrA, Hlk, False))
Ban3 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = Ban1 + Ban2 - Ban3
End If
End If
ElseIf acc = "CC420" Then 'CC420 Laba bersih disetahunkan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban1 = Ban1 * 4 / Right(qtr, 1)
Cells(rc, cc).Value = Ban1
ElseIf acc = "CC301" Then 'CC301 Pendapatan per quater
If Right(qtr, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be1=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
ElseIf acc = "CC302" Then 'CC302 Laba kotor per quater
If Right(qtr, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be3=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
ElseIf acc = "CC303" Then 'CC303 Laba usaha per quater
If Right(qtr, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be5=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
ElseIf acc = "CC304" Then 'CC303 Laba bersih per quater
If Right(qtr, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "bg5=",
Vlk, False), cc)
ElseIf Right(qtr, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
ElseIf acc = "DD211" Then 'DD211 Persentase pendapatan
dibanding quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC301", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC301", Vlk, False),
.Match(qtrQ, Hlk, False))
Ban3 = .Match(qtrQ, Hlk, False)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD212" Then 'DD212 Persentase laba kotor
dibanding quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC302", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC302", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD213" Then 'DD213 Persentase laba usaha
dibanding quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD214" Then 'DD214 Persentase laba bersih
dibanding quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False),
.Match(qtrQ, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD101" Then 'DD101 Persentase pendapatan
dibanding tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD102" Then 'DD102 Persentase laba kotor
dibanding tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD103" Then 'DD103 Persentase laba usaha
dibanding tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD104" Then 'DD104 Persentase laba bersih
dibanding tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtr, Hlk, False))
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtrY, Hlk, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD317" Then 'DD317 Return of equity
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "DD318" Then 'DD318 Return of aset
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba2=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "DD910" Then 'DD910 Return of equity per quater
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False), cc)
Ban1 = Ban1 * 4
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "EE121" Then 'EE121 EPS berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE122" Then 'EE122 EPS berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE123" Then 'EE123 EPS disetahunkan
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE124" Then 'EE124 EPS dalam 12 bulan terakhir
Ban1 = .Index(Dtlk, .Match(Tic & "CC220", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE310" Then 'EE310 Kapitalisasi saham
berbanding laba usaha (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False), cc)
Ban2 = Ban2 * 4 / Right(qtr, 1)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE403" Then 'EE403 (Kapitalisasi saham +
pasiva) berbanding laba usaha (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban3 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False), cc)
Ban3 = Ban3 * 4 / Right(qtr, 1)
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = (Ban1 + Ban2) / Abs(Ban3)
End If
ElseIf acc = "EE404" Then 'EE404 (Kapitalisasi saham +
pasiva) berbanding laba usaha per quater (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban3 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False), cc)
Ban3 = Ban3 * 4
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = (Ban1 + Ban2) / Abs(Ban3)
End If
ElseIf acc = "GG201" Then 'GG201 Per berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE123", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "GG310" Then 'GG310 Per Per quater
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE122", Vlk, False), cc)
Ban2 = Ban2 * 4
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "HH101" Then 'HH101 PEG
Ban1 = .Index(Dtlk, .Match(Tic & "GG201", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "DD104", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
ElseIf Len(qtk) > 0 Then
If acc = "CC220" Then 'CC220 Laba bersih 12 bulan terakhir
If Right(qtk, 2) = "Q4" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "bg5=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q4" Then
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtkA, HqtK, False))
Ban3 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = Ban1 + Ban2 - Ban3
End If
End If
ElseIf acc = "CC420" Then 'CC420 Laba bersih disetahunkan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban1 = Ban1 * 4 / Right(qtk, 1)
Cells(rc, cc).Value = Ban1
ElseIf acc = "CC301" Then 'CC301 Pendapatan per quater
If Right(qtk, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be1=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
ElseIf acc = "CC302" Then 'CC302 Laba kotor per quater
If Right(qtk, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be3=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
ElseIf acc = "CC303" Then 'CC303 Laba usaha per quater
If Right(qtk, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "be5=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
ElseIf acc = "CC304" Then 'CC303 Laba bersih per quater
If Right(qtk, 2) = "Q1" Then
Cells(rc, cc).Value = .Index(Dtlk, .Match(Tic & "bg5=",
Vlk, False), cc)
ElseIf Right(qtk, 2) <> "Q1" Then
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 - Ban2
End If
End If
ElseIf acc = "DD211" Then 'DD211 Persentase pendapatan
dibanding quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC301", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC301", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD212" Then 'DD212 Persentase laba kotor
dibanding quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC302", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC302", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD213" Then 'DD213 Persentase laba usaha
dibanding quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD214" Then 'DD214 Persentase laba bersih
dibanding quater sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False),
.Match(qtkQ, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD101" Then 'DD101 Persentase pendapatan
dibanding tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be1=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD102" Then 'DD102 Persentase laba kotor
dibanding tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be3=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD103" Then 'DD103 Persentase laba usaha
dibanding tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD104" Then 'DD104 Persentase laba bersih
dibanding tahun sebelumnya
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtk, HqtK, False))
Ban2 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False),
.Match(qtkY, HqtK, False))
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = (Ban1 - Ban2) / Abs(Ban2) * 100
End If
ElseIf acc = "DD317" Then 'DD317 Return of equity
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "DD318" Then 'DD318 Return of aset
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba2=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "DD910" Then 'DD910 Return of equity per quater
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False), cc)
Ban1 = Ban1 * 4
Ban2 = .Index(Dtlk, .Match(Tic & "ba6=", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2) * 100
End If
ElseIf acc = "EE121" Then 'EE121 EPS berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "bg5=", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE122" Then 'EE122 EPS berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "CC304", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE123" Then 'EE123 EPS disetahunkan
Ban1 = .Index(Dtlk, .Match(Tic & "CC420", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE124" Then 'EE124 EPS dalam 12 bulan terakhir
Ban1 = .Index(Dtlk, .Match(Tic & "CC220", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "BB202", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE310" Then 'EE310 Kapitalisasi saham
berbanding laba usaha (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False), cc)
Ban2 = Ban2 * 4 / Right(qtk, 1)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "EE403" Then 'EE403 (Kapitalisasi saham +
pasiva) berbanding laba usaha (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban3 = .Index(Dtlk, .Match(Tic & "be5=", Vlk, False), cc)
Ban3 = Ban3 * 4 / Right(qtk, 1)
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = (Ban1 + Ban2) / Abs(Ban3)
End If
ElseIf acc = "EE404" Then 'EE404 (Kapitalisasi saham +
pasiva) berbanding laba usaha per quater (laba usaha di setahunkan)
Ban1 = .Index(Dtlk, .Match(Tic & "CC410", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "ba3=", Vlk, False), cc)
Ban3 = .Index(Dtlk, .Match(Tic & "CC303", Vlk, False), cc)
Ban3 = Ban3 * 4
If Ban1 <> 0 And Ban2 <> 0 And Ban3 <> 0 Then
Cells(rc, cc).Value = (Ban1 + Ban2) / Abs(Ban3)
End If
ElseIf acc = "GG201" Then 'GG201 Per berjalan
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE123", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "GG310" Then 'GG310 Per Per quater
Ban1 = .Index(Dtlk, .Match(Tic & "AT100", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "EE122", Vlk, False), cc)
Ban2 = Ban2 * 4
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
ElseIf acc = "HH101" Then 'HH101 PEG
Ban1 = .Index(Dtlk, .Match(Tic & "GG201", Vlk, False), cc)
Ban2 = .Index(Dtlk, .Match(Tic & "DD104", Vlk, False), cc)
If Ban1 <> 0 And Ban2 <> 0 Then
Cells(rc, cc).Value = Ban1 / Abs(Ban2)
End If
End If
End If
Ban1 = 0
Ban2 = 0
Ban3 = 0
ban4 = 0
If Cells(rc, 1) <> Cells(rc + 1, 1) Then
Tic = ""
matauang = ""
qtk = ""
End If
End If
Next rc
Next xc
End With
cmd = Timer - cmd
Cells(3, 10).Value = cmd
Cells(4, 10).Value = Time
End Sub
