Kalo pakai macro, bisa menggunakan code di bawah:
Sub v()
Const RUMUS_C4 As String = "=VLOOKUP(B4,a!$B$4:$D$6,2,FALSE)"
Const RUMUS_D4 As String = "=VLOOKUP(B4,a!$B$4:$D$6,3,FALSE)"
Const KOLOM_ISIAN As String = "B", KOLOM_TANGGAL As String = "C",
KOLOM_JUMLAH As String = "D"
Const barisAwal As Long = 4
Const barisMax As Long = 10000
Const shtTarget As String = "b"
With Worksheets(shtTarget)
'mencari baris input terakhir
Dim n As Long
n = .Range(KOLOM_ISIAN & barisMax).End(xlUp).Row
'tentukan range tanggal (kolom c) dan range jumlah (kolom d)
Dim rgTanggal As Range, rgJumlah As Range
Set rgTanggal = .Range(KOLOM_TANGGAL & barisAwal & ":" & KOLOM_TANGGAL
& n)
Set rgJumlah = .Range(KOLOM_JUMLAH & barisAwal & ":" & KOLOM_JUMLAH &
n)
'mengisi formula
rgTanggal.Formula = RUMUS_C4
rgJumlah.Formula = RUMUS_D4
'rubah menjadi value
rgTanggal.Value = rgTanggal.Value
rgJumlah.Value = rgJumlah.Value
End With
End Sub
menampilkan hasil formula tanpa menampilkan rumus di cell excel(macro).xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
