maap pak yono.. saya coba2 sendiri keknya sudah berhasil...makasih banyak...
ntar kalo ada yg mau saya tanyakan lagi.. jangan marah ya kalo banyak nanya..
hehe.. thx alot.
Pada Jumat, 9 Oktober 2015 19:00, "Ivan Sebastian layona...@yahoo.co.id
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
terima kasih pak yono.. cuman kayaknya masih ada miss sedikit nih.. bagian
report kolom i saya pinginnya bisa langsung keluar statusnya "tidak terbayar"
setiap kotak report muncul.. maap kl banyak minta bantuannya pak..terima
kasih...saya cuman bisanya via record makro.. kalo rumus beginian masih angkat
tangan... hahaha.. maklum belajar otodidak..
Pada Jumat, 9 Oktober 2015 16:48, "SUYONO reza.suy...@lge.com
[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Sore Pak Ivan Coba seperti ini. Sub ISI() Dim rowData As Integer Dim
rowHasil As Integer Dim rowPaste As Integer 'Jarak setiap nota 25 row. 'row
dihapus samape 35000 Rows("4:35000").Delete Shift:=xlUp ' titik awal
pengambilan data rowData = 22 rowHasil = 2 rowPaste = 1 Range("A1:I3").Copy
With Sheets("CETAK NOTA") Do Until .Cells(rowData, 7).Value = ""
Range("A1:I3").Copy Cells(rowPaste, 1) 'kode toko
Cells(rowHasil, 1).Value = .Cells(rowData - 21, 4).Value 'nama toko
Cells(rowHasil, 2).Value = .Cells(rowData - 20, 7).Value& " " &
.Cells(rowData - 19, 7).Value 'tanggal Cells(rowHasil, 3).Value
= .Cells(rowData - 21, 7).Value 'tempo Cells(rowHasil, 4).Value
= .Cells(rowData - 20, 3).Value 'jumlah Cells(rowHasil,
5).Value = .Cells(rowData, 7).Value 'disc rowData = rowData +
25 rowHasil = rowHasil + 4 rowPaste = rowPaste + 4 Loop End
With End Sub Salam Suyono From:belajar-excel@yahoogroups.com
[mailto:belajar-excel@yahoogroups.com]
Sent: Thursday, October 08, 2015 10:52 PM
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] tolong dibantu perbaiki rumus makrovbanya... [1
Attachment] pak yono.. ada yg mau saya tanyakan lagi... rumus sub isi ()
padamodul 1 apa bisa disederhanakan lagi? saya ingin dengan menekan tombol isi
maka hasilnya seperti padasheet hasil.. file saya lampirkan... terima kasih
pak.. Pada Rabu, 7 Oktober 2015 12:17,"Ivansl layona...@yahoo.co.id
[belajar-excel]"<belajar-excel@yahoogroups.com> menulis: Waow.. Sempurna
sekali rumusnya pak... Sesuai yg saya harapkan...Thx banget pak..
Sent from my PC
On 7 Okt 2015, at 08.21, SUYONO reza.suy...@lge.com [belajar-excel]
<belajar-excel@yahoogroups.com>wrote:
Hehe.., coba seperti ini. Sub Rectangle7_Click() Dim lastRow As Long, i
As Long, j AsLong Dim idxrow As Integer Dim jml As Integer Dim item As
String Dim getVal As Integer Dim satuan As String
Range("I2:P473").ClearContents lastRow = 597 For i = 1 To lastRow
item =Sheets("CETAK NOTA").Cells(i, 2).Value satuan =Sheets("CETAK
NOTA").Cells(i, 4).Value If idxItem(item) >1 Then getVal =
Sheets("CETAK NOTA").Cells(i, 3).Value idxrow = idxItem(item)
jml = Application.WorksheetFunction.CountA(Range(Cells(idxrow, 9),Cells(idxrow,
16))) jml = jml + 9 Sheets("REKAP FULL").Cells(idxrow,
jml).Value = getVal CallWarna_Satuan(satuan, Sheets("REKAP
FULL").Cells(idxrow, jml)) End If Next i End Sub Sub
Warna_Satuan(satuan As String, rngAs Range) With rng.Font Select Case
satuan Case "pcs" .Color = -16776961 .TintAndShade
= 0 Case "DOS" .ThemeColor = xlThemeColorLight1
.TintAndShade = 0 ‘jika adasatuan lain bisa ditambahkan disini. End
Select End With End Sub Public Function idxItem(item As String)As Integer
On Error GoTo Err idxItem =Application.WorksheetFunction.Match(item,
Sheets("REKAP FULL").Columns("C:C"),0) Exit Function Err: idxItem = 0
End Function From:
belajar-excel@yahoogroups.com[mailto:belajar-excel@yahoogroups.com]
Sent: Tuesday, October 06, 2015 9:55 PM
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] tolong dibantu perbaiki rumus makrovbanya... [1
Attachment] pak ada yg mau saya tanyakan lagi nih... detailnya saya tulis
difilenya... mohon bantuannya lagi... thx.. Pada Selasa, 6 Oktober 2015
19:08,"Ivan Sebastian layona...@yahoo.co.id[belajar-excel]"
<belajar-excel@yahoogroups.com> menulis: ups ternyata saya yg salah pak
suyono... terima kasihbantuannya... cocok sesuai yg saya harapkan... rumus yg
ini lupa saya copy...pantes aja hasilnya error...hahaha.. makasih pak.. top
rumusnya.. Public FunctionidxItem(item As String) As Integer On Error GoToErr
idxItem = Application.WorksheetFunction.Match(item,
Sheets("REKAPFULL").Columns("C:C"), 0) Exit Function Err: idxItem = 0 End
Function Pada Selasa, 6 Oktober 2015 18:45, IvanSebastian
<layona...@yahoo.co.id>menulis: sudah saya coba hasilnya... sub or function
not defined...kayaknya ada yg salah nih.. Pada Selasa, 6 Oktober 2015
18:28,"SUYONO reza.suy...@lge.com[belajar-excel]"
<belajar-excel@yahoogroups.com> menulis: Coba diganti seperti ini pak,
semogasesuai keinginan. Sub Rectangle7_Click() Dim lastRow As Long, i As
Long, j AsLong Dim idxrow As Integer Dim jml As Integer Dim item As String Dim
getVal As Integer Range("I2:P473").ClearContents lastRow = 597 For i = 1 To
lastRow item =Sheets("CETAK NOTA").Cells(i, 2).Value If idxItem(item)
>1 Then getVal = Sheets("CETAK NOTA").Cells(i, 3).Value idxrow =
idxItem(item) jml =
Application.WorksheetFunction.CountA(Range(Cells(idxrow, 9), Cells(idxrow,16)))
jml = jml + 9 Sheets("REKAP FULL").Cells(idxrow, jml).Value =
getVal End If Next i End Sub ‘======================================
‘fungsi untuk mencari indek item. ‘======================================
Public Function idxItem(item As String)As Integer On Error GoTo Err idxItem
=Application.WorksheetFunction.Match(item, Sheets("REKAPFULL").Columns("C:C"),
0) Exit Function Err: idxItem = 0 End Function From:
belajar-excel@yahoogroups.com[mailto:belajar-excel@yahoogroups.com]
Sent: Tuesday, October 06, 2015 1:35 PM
To: belajar-excel@yahoogroups.com
Subject: Bls: [belajar-excel] tolong dibantu perbaiki rumus makrovbanya... [1
Attachment] terima kasih atas bantuannya pak suyono... kalo saya coba
bikin ke bentuk dengan model lain lg... bisa bantuperbaiki rumusnya... kurang
lebih hampir sama.. cuman jumlah item lebihbanyak... thx.. data saya
lampirkan... Pada Selasa, 6 Oktober 2015 7:34,"SUYONO
reza.suy...@lge.com[belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
Pagi Pak Ivan Coba ditambahin fungsi counta danditambah 8 sebagai colom
pertama pengisian data. Sub Rectangle6_Click() Range("h2:ae8").ClearContents
Application.ScreenUpdating = False Dim lastRow As Long, i As Long, j AsLong Dim
jml As Integer lastRow = 597 j = 1 For x = 2 To 7 For i = 1 To lastRow
If InStr(Sheets(2).Range("B" & i).Value, Cells(x, 2).Value) Then
jml = Application.WorksheetFunction.CountA(Sheets(3).Range(Cells(x, 8),Cells(x,
31))) jml = jml + 8 Sheets(2).Cells(i, 3).Copy
Destination:=Sheets(3).Cells(x, jml) j = j + 1 End If
Next i Next x Application.ScreenUpdating = True End Sub Salam Yono From:
belajar-excel@yahoogroups.com[mailto:belajar-excel@yahoogroups.com]
Sent: Monday, October 05, 2015 11:03 PM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] tolong dibantu perbaiki rumus makro vbanya...[1
Attachment] hello master2 excel.. ini saya lg coba2 rumus vba cuman vba
untuktombol updatenya saya rasa lom sempurna... sekiranya tolong dibantu
diperbaikisupaya jadi sempurna...file saya lampirkan... thx
#yiv1852768012 #yiv1852768012 -- #yiv1852768012ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1852768012
#yiv1852768012ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1852768012
#yiv1852768012ygrp-mkp #yiv1852768012hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1852768012 #yiv1852768012ygrp-mkp #yiv1852768012ads
{margin-bottom:10px;}#yiv1852768012 #yiv1852768012ygrp-mkp .yiv1852768012ad
{padding:0 0;}#yiv1852768012 #yiv1852768012ygrp-mkp .yiv1852768012ad p
{margin:0;}#yiv1852768012 #yiv1852768012ygrp-mkp .yiv1852768012ad a
{color:#0000ff;text-decoration:none;}#yiv1852768012 #yiv1852768012ygrp-sponsor
#yiv1852768012ygrp-lc {font-family:Arial;}#yiv1852768012
#yiv1852768012ygrp-sponsor #yiv1852768012ygrp-lc #yiv1852768012hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1852768012
#yiv1852768012ygrp-sponsor #yiv1852768012ygrp-lc .yiv1852768012ad
{margin-bottom:10px;padding:0 0;}#yiv1852768012 #yiv1852768012actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1852768012
#yiv1852768012activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1852768012
#yiv1852768012activity span {font-weight:700;}#yiv1852768012
#yiv1852768012activity span:first-child
{text-transform:uppercase;}#yiv1852768012 #yiv1852768012activity span a
{color:#5085b6;text-decoration:none;}#yiv1852768012 #yiv1852768012activity span
span {color:#ff7900;}#yiv1852768012 #yiv1852768012activity span
.yiv1852768012underline {text-decoration:underline;}#yiv1852768012
.yiv1852768012attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1852768012 .yiv1852768012attach div a
{text-decoration:none;}#yiv1852768012 .yiv1852768012attach img
{border:none;padding-right:5px;}#yiv1852768012 .yiv1852768012attach label
{display:block;margin-bottom:5px;}#yiv1852768012 .yiv1852768012attach label a
{text-decoration:none;}#yiv1852768012 blockquote {margin:0 0 0
4px;}#yiv1852768012 .yiv1852768012bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1852768012
.yiv1852768012bold a {text-decoration:none;}#yiv1852768012 dd.yiv1852768012last
p a {font-family:Verdana;font-weight:700;}#yiv1852768012 dd.yiv1852768012last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1852768012
dd.yiv1852768012last p span.yiv1852768012yshortcuts
{margin-right:0;}#yiv1852768012 div.yiv1852768012attach-table div div a
{text-decoration:none;}#yiv1852768012 div.yiv1852768012attach-table
{width:400px;}#yiv1852768012 div.yiv1852768012file-title a, #yiv1852768012
div.yiv1852768012file-title a:active, #yiv1852768012
div.yiv1852768012file-title a:hover, #yiv1852768012 div.yiv1852768012file-title
a:visited {text-decoration:none;}#yiv1852768012 div.yiv1852768012photo-title a,
#yiv1852768012 div.yiv1852768012photo-title a:active, #yiv1852768012
div.yiv1852768012photo-title a:hover, #yiv1852768012
div.yiv1852768012photo-title a:visited {text-decoration:none;}#yiv1852768012
div#yiv1852768012ygrp-mlmsg #yiv1852768012ygrp-msg p a
span.yiv1852768012yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1852768012
.yiv1852768012green {color:#628c2a;}#yiv1852768012 .yiv1852768012MsoNormal
{margin:0 0 0 0;}#yiv1852768012 o {font-size:0;}#yiv1852768012
#yiv1852768012photos div {float:left;width:72px;}#yiv1852768012
#yiv1852768012photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv1852768012
#yiv1852768012photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1852768012
#yiv1852768012reco-category {font-size:77%;}#yiv1852768012
#yiv1852768012reco-desc {font-size:77%;}#yiv1852768012 .yiv1852768012replbq
{margin:4px;}#yiv1852768012 #yiv1852768012ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv1852768012 #yiv1852768012ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1852768012
#yiv1852768012ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1852768012
#yiv1852768012ygrp-mlmsg select, #yiv1852768012 input, #yiv1852768012 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv1852768012
#yiv1852768012ygrp-mlmsg pre, #yiv1852768012 code {font:115%
monospace;}#yiv1852768012 #yiv1852768012ygrp-mlmsg *
{line-height:1.22em;}#yiv1852768012 #yiv1852768012ygrp-mlmsg #yiv1852768012logo
{padding-bottom:10px;}#yiv1852768012 #yiv1852768012ygrp-msg p a
{font-family:Verdana;}#yiv1852768012 #yiv1852768012ygrp-msg
p#yiv1852768012attach-count span {color:#1E66AE;font-weight:700;}#yiv1852768012
#yiv1852768012ygrp-reco #yiv1852768012reco-head
{color:#ff7900;font-weight:700;}#yiv1852768012 #yiv1852768012ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv1852768012 #yiv1852768012ygrp-sponsor
#yiv1852768012ov li a {font-size:130%;text-decoration:none;}#yiv1852768012
#yiv1852768012ygrp-sponsor #yiv1852768012ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv1852768012
#yiv1852768012ygrp-sponsor #yiv1852768012ov ul {margin:0;padding:0 0 0
8px;}#yiv1852768012 #yiv1852768012ygrp-text
{font-family:Georgia;}#yiv1852768012 #yiv1852768012ygrp-text p {margin:0 0 1em
0;}#yiv1852768012 #yiv1852768012ygrp-text tt {font-size:120%;}#yiv1852768012
#yiv1852768012ygrp-vital ul li:last-child {border-right:none
!important;}#yiv1852768012
