Hai Supri,
Coba hal berikut :
1. ke worksheet hasil yang sudah ada formula filteringnya
2. copy baris 8 ke baris 2
3. mulai record macro
4. blok seluruh area output dibawah header output (header jangan diblok)
5. hapus (klik kanan pilih clear contents)
6. blok baris 2 -> klik kanan -> Copy
7. blok baris 8 sampai baris 100 misalnya -> tekan CTRL V
8. blok baris 8 sampai baris 100 misalnya -> klik kanan pilih Copy
9. blok baris 8 sampai baris 100 misalnya -> klik kanan pilih PasteSpecial
-> pilih Values -> OK
10. stop record macro
11. hide baris 2 bila perlu
12. panggil prosedur hasil record macro pada event change saat cell pilihan
berubah nilai
13. save as file dengan tipe .xlsm (enable macro)
Selamat mencoba.
Wassalam,
Kid.
On Mon, Nov 24, 2014 at 8:03 PM, supri anto supriantohdl07ex...@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
>
> Mohon maaf pak Hendrik baru membuka email... ^_^
>
> file terlampir, adalah file dari sini juga yang coba saya modif
> filteringnya...
>
> On 11/21/14, Hendrik Karnadi hendrikkarn...@yahoo.com [belajar-excel]
> <belajar-excel@yahoogroups.com> wrote:
> > Kalau judul kan biasanya berupa text, bisa share dummy filenya pak?
> >
> > Salam,
> > HK
> >
> >
> >
> > Sent from Samsung Mobile
> >
> > <div>-------- Original message --------</div><div>From: "supri anto
> > supriantohdl07ex...@gmail.com [belajar-excel]"
> > <belajar-excel@yahoogroups.com> </div><div>Date:21/11/2014 15:16
> > (GMT+07:00) </div><div>To: belajar-excel@yahoogroups.com
> </div><div>Subject:
> > Re: Fw: [belajar-excel] membuat no urut dengan macro </div><div>
> > </div>Terima kasih pak Hendrik....
> > Oya,saya ingin bertanya lagi. Bagaimana jika di Kolom B1 berisi (Value
> > Formula)
> > saya coba utak-atik di bagian:
> >
> > If Len(.Value) <> 0
> >
> > tapi saya tidak berhasil karena saya belum memahami fungsi-fungsi dari
> > vba...
> >
> > Mohon solusinya...
> >
> > On 11/21/14, hendrik karnadi hendrikkarn...@yahoo.com [belajar-excel]
> > <belajar-excel@yahoogroups.com> wrote:
> >> Menurut saya polanya sama pak Supri...Karena ada baris judul maka
> >> penomorannya dimulai dari baris 2 (.Row>1). Nomornya juga harus
> dikurangi
> >> 1
> >> (.Row-1)
> >> Salam,HK
> >>
> >> ----- Forwarded Message -----
> >> From: "supri anto supriantohdl07ex...@gmail.com [belajar-excel]"
> >> <belajar-excel@yahoogroups.com>
> >> To: belajar-excel@yahoogroups.com
> >> Sent: Friday, 21 November 2014, 7:14
> >> Subject: Re: [belajar-excel] membuat no urut dengan macro
> >>
> >> mba egi saya telah mencoba menambahkan
> >> fungsi row sehingga menjadi :
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> With Target
> >> If .Column = 2 Then
> >> If .Row > 1 Then
> >> If Len(.Value) <> 0 Then
> >
> >> .Offset(, -1).Value = .Row - 1
> >> End If
> >> End If
> >> End If
> >> End With
> >> End Sub
> >>
> >> berhasil...
> >> tapi apakah sama polanya menurut mba egi....
> >>
> >> On 11/21/14, supri anto <supriantohdl07ex...@gmail.com> wrote:
> >>> Alhamdulillah...
> >>> terima kasih atas tanggapannya pak Hendrik dan mba egi...
> >>>
> >>> pak hendrik... Oya, saya lupa Headernya kolom di A1 dan B1
> >>> Range("A1:A" saya coba ganti menjadi Range("A2:A" tapi malah restar...
> >>>
> >>> mba egi... bagaimana jika ada Headernya di kolom A1 dan B1
> >>>
> >>> On 11/20/14, egidia seftyanaputri egidia.s.pu...@gmail.com
> >>> [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
> >>>> Wa'alaikumsalam wr. wb.,
> >>>>
> >>>> coba cekrip berikut ini:
> >>>>
> >>>> Private Sub Worksheet_Change(ByVal Target As Range)
> >>>> With Target
> >>>> If .Column = 2 Then
> >>>> If Len(.Value) <> 0 Then
> >>>> .Offset(, -1).Value = .Row
> >>>> End If
> >>>> End If
> >>>> End With
> >>>> End Sub
> >>>>
> >>>> Wassalamu'alaikum wr wb.
> >>>>
> >>>> Egi
> >>>>
> >>>> Pada 20 November 2014 11.02, supri anto supriantohdl07ex...@gmail.com
> >>>> [belajar-excel] <belajar-excel@yahoogroups.com> menulis:
> >>>>
> >>>>>
> >>>>>
> >>>>> Assalamu'alaikum wr wb...
> >>>>> Para Master Excel....
> >>>>>
> >>>>> Mohon bantuannya,.
> >>>>> Bagaimana ya membuat no urut otomatis dengan macro.
> >>>>> jika kolom B terisi data maka kolom A akan otomatis No urutnya....
> >>>>>
> >>>>> Terima kasih
> >>>>> salam
> >>>>> supri
> >>>>>
> >>>>>
> >>>>
> >>>
> >> #yiv2302025197 #yiv2302025197 -- #yiv2302025197ygrp-mkp {border:1px
> solid
> >> #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2302025197
> >> #yiv2302025197ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2302025197
> >> #yiv2302025197ygrp-mkp #yiv2302025197hd
> >>
> {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
> >> 0;}#yiv2302025197 #yiv2302025197ygrp-mkp #yiv2302025197ads
> >> {margin-bottom:10px;}#yiv2302025197 #yiv2302025197ygrp-mkp
> >> .yiv2302025197ad
> >> {padding:0 0;}#yiv2302025197 #yiv2302025197ygrp-mkp .yiv2302025197ad p
> >> {margin:0;}#yiv2302025197 #yiv2302025197ygrp-mkp .yiv2302025197ad a
> >> {color:#0000ff;text-decoration:none;}#yiv2302025197
> >> #yiv2302025197ygrp-sponsor #yiv2302025197ygrp-lc
> >> {font-family:Arial;}#yiv2302025197 #yiv2302025197ygrp-sponsor
> >> #yiv2302025197ygrp-lc #yiv2302025197hd {margin:10px
> >> 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2302025197
> >> #yiv2302025197ygrp-sponsor #yiv2302025197ygrp-lc .yiv2302025197ad
> >> {margin-bottom:10px;padding:0 0;}#yiv2302025197 #yiv2302025197actions
> >> {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2302025197
> >> #yiv2302025197activity
> >>
> {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2302025197
> >
> >> #yiv2302025197activity span {font-weight:700;}#yiv2302025197
> >> #yiv2302025197activity span:first-child
> >> {text-transform:uppercase;}#yiv2302025197 #yiv2302025197activity span a
> >> {color:#5085b6;text-decoration:none;}#yiv2302025197
> #yiv2302025197activity
> >> span span {color:#ff7900;}#yiv2302025197 #yiv2302025197activity span
> >> .yiv2302025197underline {text-decoration:underline;}#yiv2302025197
> >> .yiv2302025197attach
> >> {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
> >> 0;width:400px;}#yiv2302025197 .yiv2302025197attach div a
> >> {text-decoration:none;}#yiv2302025197 .yiv2302025197attach img
> >> {border:none;padding-right:5px;}#yiv2302025197 .yiv2302025197attach
> label
> >> {display:block;margin-bottom:5px;}#yiv2302025197 .yiv2302025197attach
> >> label
> >> a {text-decoration:none;}#yiv2302025197 blockquote {margin:0 0 0
> >> 4px;}#yiv2302025197 .yiv2302025197bold
> >> {font-family:Arial;font-size:13px;font-weight:700;}#yiv2302025197
> >> .yiv2302025197bold a {text-decoration:none;}#yiv2302025197
> >> dd.yiv2302025197last p a
> >> {font-family:Verdana;font-weight:700;}#yiv2302025197
> dd.yiv2302025197last
> >> p
> >> span
> >> {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2302025197
> >> dd.yiv2302025197last p span.yiv2302025197yshortcuts
> >> {margin-right:0;}#yiv2302025197 div.yiv2302025197attach-table div div a
> >> {text-decoration:none;}#yiv2302025197 div.yiv2302025197attach-table
> >> {width:400px;}#yiv2302025197 div.yiv2302025197file-title a,
> #yiv2302025197
> >> div.yiv2302025197file-title a:active, #yiv2302025197
> >> div.yiv2302025197file-title a:hover, #yiv2302025197
> >
> >> div.yiv2302025197file-title a:visited
> >> {text-decoration:none;}#yiv2302025197
> >> div.yiv2302025197photo-title a, #yiv2302025197
> >> div.yiv2302025197photo-title
> >> a:active, #yiv2302025197 div.yiv2302025197photo-title a:hover,
> >> #yiv2302025197 div.yiv2302025197photo-title a:visited
> >> {text-decoration:none;}#yiv2302025197 div#yiv2302025197ygrp-mlmsg
> >> #yiv2302025197ygrp-msg p a span.yiv2302025197yshortcuts
> >> {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2302025197
> >> .yiv2302025197green {color:#628c2a;}#yiv2302025197
> .yiv2302025197MsoNormal
> >> {margin:0 0 0 0;}#yiv2302025197 o {font-size:0;}#yiv2302025197
> >> #yiv2302025197photos div {float:left;width:72px;}#yiv2302025197
> >> #yiv2302025197photos div div {border:1px solid
> >> #666666;height:62px;overflow:hidden;width:62px;}#yiv2302025197
> >> #yiv2302025197photos div label
> >>
> {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv2302025197
> >> #yiv2302025197reco-category {font-size:77%;}#yiv2302025197
> >> #yiv2302025197reco-desc {font-size:77%;}#yiv2302025197
> >> .yiv2302025197replbq
> >> {margin:4px;}#yiv2302025197 #yiv2302025197ygrp-actbar div a:first-child
> >> {margin-right:2px;padding-right:5px;}#yiv2302025197
> >> #yiv2302025197ygrp-mlmsg
> >> {font-size:13px;font-family:Arial, helvetica, clean,
> >> sans-serif;}#yiv2302025197 #yiv2302025197ygrp-mlmsg table
> >
> >> {font-size:inherit;font:100%;}#yiv2302025197 #yiv2302025197ygrp-mlmsg
> >> select, #yiv2302025197 input, #yiv2302025197 textarea {font:99% Arial,
> >> Helvetica, clean, sans-serif;}#yiv2302025197 #yiv2302025197ygrp-mlmsg
> pre,
> >> #yiv2302025197 code {font:115% monospace;}#yiv2302025197
> >> #yiv2302025197ygrp-mlmsg * {line-height:1.22em;}#yiv2302025197
> >> #yiv2302025197ygrp-mlmsg #yiv2302025197logo
> >> {padding-bottom:10px;}#yiv2302025197 #yiv2302025197ygrp-msg p a
> >> {font-family:Verdana;}#yiv2302025197 #yiv2302025197ygrp-msg
> >> p#yiv2302025197attach-count span
> >> {color:#1E66AE;font-weight:700;}#yiv2302025197 #yiv2302025197ygrp-reco
> >> #yiv2302025197reco-head {color:#ff7900;font-weight:700;}#yiv2302025197
> >> #yiv2302025197ygrp-reco {margin-bottom:20px;padding:0px;}#yiv2302025197
> >> #yiv2302025197ygrp-sponsor #yiv2302025197ov li a
> >> {font-size:130%;text-decoration:none;}#yiv2302025197
> >> #yiv2302025197ygrp-sponsor #yiv2302025197ov li
> >> {font-size:77%;list-style-type:square;padding:6px 0;}#yiv2302025197
> >> #yiv2302025197ygrp-sponsor #yiv2302025197ov ul {margin:0;padding:0 0 0
> >> 8px;}#yiv2302025197 #yiv2302025197ygrp-text
> >> {font-family:Georgia;}#yiv2302025197 #yiv2302025197ygrp-text p
> {margin:0 0
> >> 1em 0;}#yiv2302025197 #yiv2302025197ygrp-text tt
> >> {font-size:120%;}#yiv2302025197 #yiv2302025197ygrp-vital ul
> li:last-child
> >> {border-right:none !important;}#yiv2302025197
> >>
> >>
> >
>
>
