Minggu, 13 November 2011

[belajar-excel] Digest Number 1397

Messages In This Digest (7 Messages)

1a.
Mega Formula untuk Fungsi Terbilang From: zainul_ulum[at]yahoo[dot]com
1b.
Re: Mega Formula untuk Fungsi Terbilang From: summonery
1c.
Re: Mega Formula untuk Fungsi Terbilang From: zainul_ulum[at]yahoo[dot]com
1d.
Re: Mega Formula untuk Fungsi Terbilang From: summonery
2.
Kombinasi excel dan Power Point dgn macro. From: hartini_80@yahoo.co.id
3a.
VBA Debug From: hartini_80@yahoo.co.id
3b.
Re: VBA Debug From: Kid Mr.

Messages

1a.

Mega Formula untuk Fungsi Terbilang

Posted by: "zainul_ulum[at]yahoo[dot]com" zainul_ulum@yahoo.com   zainul_ulum

Sat Nov 12, 2011 11:07 am (PST)



Be-Exceler,
Mohon bantuan untuk menguji fungsi mega formula yang aku buat untuk fungsi
terbilang untuk angka bulat dari 1 sampai 999999999

Fungsi ini menggunakan bantuan dua buah [static named ranges], yaitu
1. frm
refers to ="000000000"

2. ListHuruf
refers to
={"","satu","dua","tiga","empat","lima","enam","tujuh","delapan","sembilan",
"sepuluh","sebelas","dua belas","tiga belas","empat belas","lima
belas","enam belas","tujuh belas","delapan belas","sembilan belas"}

Mega Formulanya dengan asumsi angka yang akan disebut di cell A2
=SUBSTITUTE(INDEX(ListHuruf,MID(TEXT(A2,frm),1,1)+1)
&REPT(" ratus ",-MID(TEXT(A2,frm),1,3)>99)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)<20)*MID(TEXT(A2,frm),2,2)+1)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID(TEXT(A2,frm),2,1)+1)
&REPT(" puluh
",-MID(TEXT(A2,frm),2,2)>19)&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID
(TEXT(A2,frm),3,1)+1)
&REPT(" juta ",LEN(A2)>6)
&INDEX(ListHuruf,MID(TEXT(A2,frm),4,1)+1)
&REPT(" ratus ",-MID(TEXT(A2,frm),4,3)>99)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)<20)*MID(TEXT(A2,frm),5,2)*(A2>2000)
+1)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),5,1)*(A2>2000)
+1)
&REPT(" puluh ",-MID(TEXT(A2,frm),5,2)>19)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),6,1)+1)
&REPT("seribu ",(A2<2000)*(LEN(A2)=4))
&REPT(" ribu ",(A2>2000))
&INDEX(ListHuruf,MID(TEXT(A2,frm),7,1)+1)
&REPT(" ratus ",-MID(TEXT(A2,frm),7,3)>99)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)<20)*MID(TEXT(A2,frm),8,2)+1)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),8,1)+1)
&REPT(" puluh ",-MID(TEXT(A2,frm),8,2)>19)
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),9,1)+1),"satu
ratus","seratus")

Contoh file terlampir
Terimakasih
-zainul-

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
1b.

Re: Mega Formula untuk Fungsi Terbilang

Posted by: "summonery" summonery@yahoo.com   summonery

Sun Nov 13, 2011 4:29 am (PST)



silakan dicoba untuk: 2,000 1,000,001 11,000,001 1,000,000,001
1,000,100,101 100,000,000,000 111,000,000,000 111,000,000,001

>semoga bermanfaat

*topik tenggelam dalam topik
--- In belajar-excel@yahoogroups.com, "zainul_ulum[at]yahoo[dot]com"
<zainul_ulum@...> wrote:
>
> Be-Exceler,
> Mohon bantuan untuk menguji fungsi mega formula yang aku buat untuk
fungsi
> terbilang untuk angka bulat dari 1 sampai 999999999
>
> Fungsi ini menggunakan bantuan dua buah [static named ranges], yaitu
> 1. frm
> refers to ="000000000"
>
> 2. ListHuruf
> refers to
>
={"","satu","dua","tiga","empat","lima","enam","tujuh","delapan","sembil\
an",
> "sepuluh","sebelas","dua belas","tiga belas","empat belas","lima
> belas","enam belas","tujuh belas","delapan belas","sembilan belas"}
>
> Mega Formulanya dengan asumsi angka yang akan disebut di cell A2
> =SUBSTITUTE(INDEX(ListHuruf,MID(TEXT(A2,frm),1,1)+1)
> &REPT(" ratus ",-MID(TEXT(A2,frm),1,3)>99)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)<20)*MID(TEXT(A2,frm),2,2)+1)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID(TEXT(A2,frm),2,1)+1)
> &REPT(" puluh
>
",-MID(TEXT(A2,frm),2,2)>19)&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)\
*MID
> (TEXT(A2,frm),3,1)+1)
> &REPT(" juta ",LEN(A2)>6)
> &INDEX(ListHuruf,MID(TEXT(A2,frm),4,1)+1)
> &REPT(" ratus ",-MID(TEXT(A2,frm),4,3)>99)
>
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)<20)*MID(TEXT(A2,frm),5,2)*(A2>2\
000)
> +1)
>
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),5,1)*(A2>2\
000)
> +1)
> &REPT(" puluh ",-MID(TEXT(A2,frm),5,2)>19)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),6,1)+1)
> &REPT("seribu ",(A2<2000)*(LEN(A2)=4))
> &REPT(" ribu ",(A2>2000))
> &INDEX(ListHuruf,MID(TEXT(A2,frm),7,1)+1)
> &REPT(" ratus ",-MID(TEXT(A2,frm),7,3)>99)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)<20)*MID(TEXT(A2,frm),8,2)+1)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),8,1)+1)
> &REPT(" puluh ",-MID(TEXT(A2,frm),8,2)>19)
>
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),9,1)+1),"s\
atu
> ratus","seratus")
>
> Contoh file terlampir
> Terimakasih
> -zainul-
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
1c.

Re: Mega Formula untuk Fungsi Terbilang

Posted by: "zainul_ulum[at]yahoo[dot]com" zainul_ulum@yahoo.com   zainul_ulum

Sun Nov 13, 2011 4:57 am (PST)



Terimakasih banyak mas Anton
Terlampir revisinya

Thanks
-zainul-

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
On Behalf Of summonery
Sent: Sunday, November 13, 2011 7:29 PM
To: belajar-excel@yahoogroups.com
Subject: [belajar-excel] Re: Mega Formula untuk Fungsi Terbilang

 
silakan dicoba untuk:
 2,000 
 1,000,001 
 11,000,001 
 1,000,000,001 
 1,000,100,101 
 100,000,000,000 
 111,000,000,000 
 111,000,000,001 

>semoga bermanfaat

*topik tenggelam dalam topik

--- In belajar-excel@yahoogroups.com, "zainul_ulum[at]yahoo[dot]com"
<zainul_ulum@...> wrote:
>
> Be-Exceler,
> Mohon bantuan untuk menguji fungsi mega formula yang aku buat untuk fungsi
> terbilang untuk angka bulat dari 1 sampai 999999999
>
> Fungsi ini menggunakan bantuan dua buah [static named ranges], yaitu
> 1. frm
> refers to ="000000000"
>
> 2. ListHuruf
> refers to
>
={"","satu","dua","tiga","empat","lima","enam","tujuh","delapan","sembilan",
> "sepuluh","sebelas","dua belas","tiga belas","empat belas","lima
> belas","enam belas","tujuh belas","delapan belas","sembilan belas"}
>
> Mega Formulanya dengan asumsi angka yang akan disebut di cell A2
> =SUBSTITUTE(INDEX(ListHuruf,MID(TEXT(A2,frm),1,1)+1)
> &REPT(" ratus ",-MID(TEXT(A2,frm),1,3)>99)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)<20)*MID(TEXT(A2,frm),2,2)+1)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID(TEXT(A2,frm),2,1)+1)
> &REPT(" puluh
>
",-MID(TEXT(A2,frm),2,2)>19)&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID
> (TEXT(A2,frm),3,1)+1)
> &REPT(" juta ",LEN(A2)>6)
> &INDEX(ListHuruf,MID(TEXT(A2,frm),4,1)+1)
> &REPT(" ratus ",-MID(TEXT(A2,frm),4,3)>99)
>
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)<20)*MID(TEXT(A2,frm),5,2)*(A2>2000)
> +1)
>
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),5,1)*(A2>2000)
> +1)
> &REPT(" puluh ",-MID(TEXT(A2,frm),5,2)>19)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),6,1)+1)
> &REPT("seribu ",(A2<2000)*(LEN(A2)=4))
> &REPT(" ribu ",(A2>2000))
> &INDEX(ListHuruf,MID(TEXT(A2,frm),7,1)+1)
> &REPT(" ratus ",-MID(TEXT(A2,frm),7,3)>99)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)<20)*MID(TEXT(A2,frm),8,2)+1)
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),8,1)+1)
> &REPT(" puluh ",-MID(TEXT(A2,frm),8,2)>19)
>
&INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),9,1)+1),"satu
> ratus","seratus")
>
> Contoh file terlampir
> Terimakasih
> -zainul-
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
1d.

Re: Mega Formula untuk Fungsi Terbilang

Posted by: "summonery" summonery@yahoo.com   summonery

Sun Nov 13, 2011 6:09 am (PST)



coba lagi,

1,200,000
2,100,000
3,100,100

--- In belajar-excel@yahoogroups.com, "zainul_ulum[at]yahoo[dot]com" <zainul_ulum@...> wrote:
>
> Terimakasih banyak mas Anton
> Terlampir revisinya
>
> Thanks
> -zainul-
>
> From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
> On Behalf Of summonery
> Sent: Sunday, November 13, 2011 7:29 PM
> To: belajar-excel@yahoogroups.com
> Subject: [belajar-excel] Re: Mega Formula untuk Fungsi Terbilang
>
>  
> silakan dicoba untuk:
>  2,000 
>  1,000,001 
>  11,000,001 
>  1,000,000,001 
>  1,000,100,101 
>  100,000,000,000 
>  111,000,000,000 
>  111,000,000,001 
>
>
> >semoga bermanfaat
>
>
>
>
> *topik tenggelam dalam topik
>
> --- In belajar-excel@yahoogroups.com, "zainul_ulum[at]yahoo[dot]com"
> <zainul_ulum@> wrote:
> >
> > Be-Exceler,
> > Mohon bantuan untuk menguji fungsi mega formula yang aku buat untuk fungsi
> > terbilang untuk angka bulat dari 1 sampai 999999999
> >
> > Fungsi ini menggunakan bantuan dua buah [static named ranges], yaitu
> > 1. frm
> > refers to ="000000000"
> >
> > 2. ListHuruf
> > refers to
> >
> ={"","satu","dua","tiga","empat","lima","enam","tujuh","delapan","sembilan",
> > "sepuluh","sebelas","dua belas","tiga belas","empat belas","lima
> > belas","enam belas","tujuh belas","delapan belas","sembilan belas"}
> >
> > Mega Formulanya dengan asumsi angka yang akan disebut di cell A2
> > =SUBSTITUTE(INDEX(ListHuruf,MID(TEXT(A2,frm),1,1)+1)
> > &REPT(" ratus ",-MID(TEXT(A2,frm),1,3)>99)
> > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)<20)*MID(TEXT(A2,frm),2,2)+1)
> > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID(TEXT(A2,frm),2,1)+1)
> > &REPT(" puluh
> >
> ",-MID(TEXT(A2,frm),2,2)>19)&INDEX(ListHuruf,(-MID(TEXT(A2,frm),2,2)>19)*MID
> > (TEXT(A2,frm),3,1)+1)
> > &REPT(" juta ",LEN(A2)>6)
> > &INDEX(ListHuruf,MID(TEXT(A2,frm),4,1)+1)
> > &REPT(" ratus ",-MID(TEXT(A2,frm),4,3)>99)
> >
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)<20)*MID(TEXT(A2,frm),5,2)*(A2>2000)
> > +1)
> >
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),5,1)*(A2>2000)
> > +1)
> > &REPT(" puluh ",-MID(TEXT(A2,frm),5,2)>19)
> > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),5,2)>19)*MID(TEXT(A2,frm),6,1)+1)
> > &REPT("seribu ",(A2<2000)*(LEN(A2)=4))
> > &REPT(" ribu ",(A2>2000))
> > &INDEX(ListHuruf,MID(TEXT(A2,frm),7,1)+1)
> > &REPT(" ratus ",-MID(TEXT(A2,frm),7,3)>99)
> > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)<20)*MID(TEXT(A2,frm),8,2)+1)
> > &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),8,1)+1)
> > &REPT(" puluh ",-MID(TEXT(A2,frm),8,2)>19)
> >
> &INDEX(ListHuruf,(-MID(TEXT(A2,frm),8,2)>19)*MID(TEXT(A2,frm),9,1)+1),"satu
> > ratus","seratus")
> >
> > Contoh file terlampir
> > Terimakasih
> > -zainul-
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (10)
2.

Kombinasi excel dan Power Point dgn macro.

Posted by: "hartini_80@yahoo.co.id" hartini_80@yahoo.co.id   hartini_80

Sat Nov 12, 2011 4:41 pm (PST)



Dear All,
Saya pernah dibuatkan oleh mba siti dan Mr. Kid, macro untuk menampilkan table di excel dengan 2 cara (lihat Lampiran), tetapi skrng saya ingin mencoba untuk menerapkan macro tersebut kedalam power point (dengan table dan chart, sesuai lampiran), cuma saya bingung dimana ya mau menempatkan VBA di power point. Rekan-rekan BeExceler ada yg bisa bantu saya? Saya tunggu jawabannya, sebelumnya saya ucapkan terima kasih.
Salam
Tiny

Powered by Telkomsel BlackBerry®

-----Original Message-----
From: dwa_spv_fin@bukitmakmur.com
Date: Sun, 13 Nov 2011 07:20:33
To: <hartini_80@yahoo.co.id>
Subject:

Best Regards,
HARTINI
Finance Department
PT. BUMA - DWA
(P) +62-21-6613636 ext. 33161
(E) dwa_spv_fin@bukitmakmur.com
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)
3a.

VBA Debug

Posted by: "hartini_80@yahoo.co.id" hartini_80@yahoo.co.id   hartini_80

Sat Nov 12, 2011 7:55 pm (PST)



Dear all,
Saya ingin menanyakan masalah macro, jika ada keterangan DEBUG itu kenapa ya? Maaf file tdk bisa saya kirim karena berhubungan dgn sallary.
Mohon bantuan penjelasannya.
Salam
Tiny
Powered by Telkomsel BlackBerry®
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
3b.

Re: VBA Debug

Posted by: "Kid Mr." mr.nmkid@gmail.com   nmkid.family@ymail.com

Sat Nov 12, 2011 11:45 pm (PST)



Opsi Debug muncul ketika ada baris kode yang tidak dapat dijalankan (error).
Selalu muncul bersama opsi End.

Baris kode yang tidak dapat dijalankan hingga memunculkan opsi Debug
disebabkan oleh banyak hal.
Debug adalah kesempatan untuk menilik baris yang bermasalah tersebut agar
dapat segera diketahui penyebabnya dan dicari solusinya.

Regards.
Kid.

2011/11/13 <hartini_80@yahoo.co.id>

> Dear all,
> Saya ingin menanyakan masalah macro, jika ada keterangan DEBUG itu kenapa
> ya? Maaf file tdk bisa saya kirim karena berhubungan dgn sallary.
> Mohon bantuan penjelasannya.
> Salam
> Tiny
> Powered by Telkomsel BlackBerry®
>
> ------------------------------------
>
> ----------------------------------------------------------
> bergabung ke milis (subscribe), kirim mail kosong ke:
> belajar-excel-subscribe@yahoogroups.com
>
> posting ke milis, kirimkan ke:
> belajar-excel@yahoogroups.com
>
> berkunjung ke web milis
> http://tech.groups.yahoo.com/group/belajar-excel/messages
>
> melihat file archive / mendownload lampiran
> http://www.mail-archive.com/belajar-excel@yahoogroups.com/
> atau (sejak 25-Apr-2011) bisa juga di :
> http://milis-belajar-excel.1048464.n5.nabble.com/
>
> menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
>
> keluar dari membership milis (UnSubscribe):
> kirim mail kosong ke belajar-excel-unsubscribe@yahoogroups.com
> ----------------------------------------------------------Yahoo!
> Groups Links
>
>
>
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! Groups

Dog Lovers Group

Connect and share with

dog owners like you

Yahoo! Groups

Parenting Zone

Resources and tips

for parents

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke:
belajar-excel@yahoogroups.com

berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe):
kirim mail kosong ke  belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Diposting oleh EXCEL FEED LEARNING di 06.18

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)