sumbernya : J:\Saham\LK.xlsm dan J:\Saham\OHLC.xlsm.
digunakan di : J:\Saham\Komparasi.xlsm.
sudah di buat, tapi semalam menjadi error lagi .
Pesan error nya : Can't find project or library
Sub Kon_isi_data_LK()
Dim KP_rng As Range, KP_rngLoop As Range
Dim Brs As Long, Klm As Long
Dim t As Long
' Dim LK
Dim LK_Lnk_rng As Range, LK_Qtr_rng As Range, LK_kur_rng As Range,
LK_dat_rng As Range
Dim LK_C As Long, LK_R As Long, CR As String
Dim x As Long
Dim O_Tgl_rng As Range, O_Tic_rng As Range, O_Cls_rng As Range, O_Lis_rng
As Range
Workbooks("LK.xlsm").Activate
Sheets("DT_LK").Activate
LK_C = Range("A1").End(xlToRight).Column
LK_R = Range("A1").End(xlDown).Row
Set LK_Lnk_rng = Sheets("DT_LK").Range("A4:A" & LK_R)
Set LK_Qtr_rng = Sheets("DT_LK").Range(Cells(1, 11), Cells(1, LK_C))
Set LK_kur_rng = Sheets("DT_LK").Range(Cells(2, 11), Cells(2, LK_C))
Set LK_dat_rng = Sheets("DT_LK").Range(Cells(4, 11), Cells(LK_R,
LK_C))
Workbooks("OHLC.xlsm").Activate
Sheets("OHLC").Range("A2").Select
x = Range("A2").End(xlDown).Row
Set O_Tgl_rng = Sheets("OHLC").Range("A2", Range("A2").End(xlDown))
Set O_Tic_rng = Sheets("OHLC").Range("B2", Range("B2").End(xlDown))
Set O_Cls_rng = Sheets("OHLC").Range("J2", Range("J2").End(xlDown))
Set O_Lis_rng = Sheets("OHLC").Range("T2:T" & x)
O_Tgl_rng.Select
O_Lis_rng.Select
Workbooks("Komparasi.xlsm").Activate
Worksheets("KP_01").Select
Set KP_rng = Sheets("KP_01").Range("A5", Range("A5").End(xlDown))
t = 5
Range("A5").Activate
For Each KP_rngLoop In KP_rng
CR = "C3"
With KP_rngLoop
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("C1"), LK_Qtr_rng, 0)
Cells(t, 3).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "D3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("D1"), LK_Qtr_rng, 0)
Cells(t, 4).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "E3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("E1"), LK_Qtr_rng, 0)
Cells(t, 5).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "F3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("F1"), LK_Qtr_rng, 0)
Cells(t, 6).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "G3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("G1"), LK_Qtr_rng, 0)
Cells(t, 7).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "H3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("H1"), LK_Qtr_rng, 0)
Cells(t, 8).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "I3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("I1"), LK_Qtr_rng, 0)
Cells(t, 9).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "J3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("J1"), LK_Qtr_rng, 0)
Cells(t, 10).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "K3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("K1"), LK_Qtr_rng, 0)
Cells(t, 11).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "N3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("N1"), LK_Qtr_rng, 0)
Cells(t, 14).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "O3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("O1"), LK_Qtr_rng, 0)
Cells(t, 15).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "P3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("P1"), LK_Qtr_rng, 0)
Cells(t, 16).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "Q3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("Q1"), LK_Qtr_rng, 0)
Cells(t, 17).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "U3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("U1"), LK_Qtr_rng, 0)
Cells(t, 21).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "V3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("V1"), LK_Qtr_rng, 0)
Cells(t, 22).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
CR = "W3"
Brs = WorksheetFunction.Match(Cells(t, 1) & Range(CR), LK_Lnk_rng,
0)
Klm = WorksheetFunction.Match(Range("W1"), LK_Qtr_rng, 0)
Cells(t, 23).Value = WorksheetFunction.Index(LK_dat_rng, Brs, Klm)
Cells(t, 18).Value =
Application.WorksheetFunction.SumIfs(O_Cls_rng, O_Tic_rng, Range("A" & t),
O_Tgl_rng, Range("R1"))
Cells(t, 19).Value =
Application.WorksheetFunction.SumIfs(O_Cls_rng, O_Tic_rng, Range("A" & t),
O_Tgl_rng, Range("S1"))
Cells(t, 20).Value =
Application.WorksheetFunction.SumIfs(O_Lis_rng, O_Tic_rng, Range("A" & t),
O_Tgl_rng, Range("T1"))
End With
t = t + 1
Next KP_rngLoop
End Sub
Mohon bantuan nya .
Salam
Lukman
--- Mods ---
Script diatas tidak dapat membantu menemukan penyebab error.
Lampiran workbook yang datanya sudah dikurangi jumlah record malah lebih
membantu.
------------
