[belajar-excel] Do Case atau select Case [2 Attachments]

Sat, 06 Jun 2015 23:16:08 -0700 

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
  • [belajar-excel] Do Case a... lkm jktind lkmjkt...@gmail.com [belajar-excel]
    • Re: [belajar-excel] ... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
    • Re: [belajar-excel] ... Toby kenzo tobyke...@yahoo.co.id [belajar-excel]
      • Re: [belajar-exc... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
        • Re: [belajar... lkm jktind lkmjkt...@gmail.com [belajar-excel]
          • Re: [bel... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
            • Re:... lkm jktind lkmjkt...@gmail.com [belajar-excel]
              • ... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
                • ... lkm jktind lkmjkt...@gmail.com [belajar-excel]
                • ... lkm jktind lkmjkt...@gmail.com [belajar-excel]
                • ... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]

Kirim email ke