Senin, 31 Desember 2012

[belajar-excel] Digest Number 1998

3 New Messages

Digest #1998
1b
Re: Membuat border, sum dan tulisan mengetahui, dengan vba by "odhyz83_assasaky@yahoo.com" odhyz83_assasaky

Messages

Sun Dec 30, 2012 8:36 pm (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Kalau begitu, mulai tahun 2013 per 1 januari, mari semangat belajar pantang
menyerah.
hehehehe...
kebanyakan BeExceller yang suka VBA lebih seneng lihat hasil usaha yang
masih acak-acakan dan belum pas sesuai target daripada lihat lembar kosong
harus diisi script... Jadi, dengan mempelajarinya setahap demi setahap,
nanti postingnya bisa ada lembar coretan script Anda sendiri lebih dulu.

Wassalam,
Kid.

2012/12/31 <odhyz83_assasaky@yahoo.com>

> **
>
>
> ** Terima kasih Mr.Kid, iya tiap cek email belum ada tanggapan, cuma bisa
> bersabar maklum saya cuma bisa bertanya. :)
>
> Salam,
> Odhy
> Sent from my BlackBerry® smartphone from Sinyal Bagus XL, Nyambung
> Teruuusss...!
> ------------------------------
> *From: * "Mr. Kid" <mr.nmkid@gmail.com>
> *Sender: * belajar-excel@yahoogroups.com
> *Date: *Mon, 31 Dec 2012 05:57:13 +0700
> *To: *<belajar-excel@yahoogroups.com>
> *ReplyTo: * belajar-excel@yahoogroups.com
> *Subject: *Re: [belajar-excel] Membuat border, sum dan tulisan
> mengetahui, dengan vba
>
>
>
> wow... sampai 7 hari belum ada yang menanggapi...
> moga-moga hanya karena para pecinta VBA sedang sibuk kejar setoran akhir
> tahun.
>
> Silakan lihat-lihat file terlampir. Moga-moga dipelajari supaya ndak
> nunggu terlalu lama.
>
> Wassalam,
> Kid.
>
>
>
> 2012/12/24 Odhy <odhyz83_assasaky@yahoo.com>
>
>> **
>>
>>
>> dear Exceler,
>> Saya ingin membuat Sebuah Tombol proses VBA, agar otomatis data (jumlah
>> data tidak tentu) yang ada itu diberi border dan di akhirnya barisnya ada
>> tulisan total dan menjumlahkan total qty. kemudian di bawah tabel akan
>> tertulis mengetahui dan dibuat oleh, seperti pada lampiran.
>>
>> Terima Kasih atas pencerahannya,
>>
>> Odhy.
>>
>> _
>>
>
>
>

Sun Dec 30, 2012 9:09 pm (PST) . Posted by:

"odhyz83_assasaky@yahoo.com" odhyz83_assasaky

Terima kasih Mr.Kid,
"Happy New Years 2013", smoga semangat 2013 mnjadi semangat baru terbarukan bagi kita bagi beExceller semua, dan smoga para Master Excel ttp ikhlas menerima keluh kesah dari para newbier. Dan pada akhirnya smoga Allah swt terus menambahkan kita Ilmu sbg amal ibadah kita, Amien...

Wassalam,
Odhy
Sent from my BlackBerry® smartphone from Sinyal Bagus XL, Nyambung Teruuusss...!

-----Original Message-----
From: "Mr. Kid" <mr.nmkid@gmail.com>
Sender: belajar-excel@yahoogroups.com
Date: Mon, 31 Dec 2012 11:35:56
To: <belajar-excel@yahoogroups.com>
Reply-To: belajar-excel@yahoogroups.com
Subject: Re: [belajar-excel] Membuat border, sum dan tulisan mengetahui,
dengan vba

Kalau begitu, mulai tahun 2013 per 1 januari, mari semangat belajar pantang
menyerah.
hehehehe...
kebanyakan BeExceller yang suka VBA lebih seneng lihat hasil usaha yang
masih acak-acakan dan belum pas sesuai target daripada lihat lembar kosong
harus diisi script... Jadi, dengan mempelajarinya setahap demi setahap,
nanti postingnya bisa ada lembar coretan script Anda sendiri lebih dulu.

Wassalam,
Kid.


2012/12/31 <odhyz83_assasaky@yahoo.com>

> **
>
>
> ** Terima kasih Mr.Kid, iya tiap cek email belum ada tanggapan, cuma bisa
> bersabar maklum saya cuma bisa bertanya. :)
>
> Salam,
> Odhy
> Sent from my BlackBerry® smartphone from Sinyal Bagus XL, Nyambung
> Teruuusss...!
> ------------------------------
> *From: * "Mr. Kid" <mr.nmkid@gmail.com>
> *Sender: * belajar-excel@yahoogroups.com
> *Date: *Mon, 31 Dec 2012 05:57:13 +0700
> *To: *<belajar-excel@yahoogroups.com>
> *ReplyTo: * belajar-excel@yahoogroups.com
> *Subject: *Re: [belajar-excel] Membuat border, sum dan tulisan
> mengetahui, dengan vba
>
>
>
> wow... sampai 7 hari belum ada yang menanggapi...
> moga-moga hanya karena para pecinta VBA sedang sibuk kejar setoran akhir
> tahun.
>
> Silakan lihat-lihat file terlampir. Moga-moga dipelajari supaya ndak
> nunggu terlalu lama.
>
> Wassalam,
> Kid.
>
>
>
> 2012/12/24 Odhy <odhyz83_assasaky@yahoo.com>
>
>> **
>>
>>
>> dear Exceler,
>> Saya ingin membuat Sebuah Tombol proses VBA, agar otomatis data (jumlah
>> data tidak tentu) yang ada itu diberi border dan di akhirnya barisnya ada
>> tulisan total dan menjumlahkan total qty. kemudian di bawah tabel akan
>> tertulis mengetahui dan dibuat oleh, seperti pada lampiran.
>>
>> Terima Kasih atas pencerahannya,
>>
>> Odhy.
>>
>> _
>>
>
>
>

Sun Dec 30, 2012 9:10 pm (PST) . Posted by:

"Adhi Buzaman"

Ok, makasih banyak atas pencerahannya. Tahun 2013 mesti banyak belajar dari

"belajar excel" neh...   hehehe
Tk 

Wassalaam

________________________________
Dari: Mr. Kid <mr.nmkid@gmail.com>
Kepada: belajar-excel@yahoogroups.com
Dikirim: Senin, 31 Desember 2012 11:27
Judul: Re: [belajar-excel] urut lagi dech


 
Wa'alaikumussalam Wr. Wb.

1. Menyusun data terurut berdasar nomor box sesuai kriteria nomor box tertentu
array formula 1 baris x 10 kolom (blok 1 baris x 10 kolom lalu tulis formula dan akhiri dengan menekan CTRL SHIFT  ENTER) :
=IFERROR(INDEX(B2:K2,MATCH(RIGHT(SMALL(IF((TEXT(B2:K2,"00")>=A1&"")*(TEXT(B2:K2,"00")<=A2&""),-MID(B2:K2&0,3,3)*100 +REPLACE(B2:K2,3,3,"")),B1:K1),2)&"*",B2:K2&"",0)),0)
>> pastikan format cell number dari area array formula black (1 baris x 10 kolom) adalah Custom berformat : General;;
     agar angka 0 akibat error value tersembunyikan
>> proses dalam formula :
a. difilter IF((textblabla)*(textblabla)
b. menyusun composite key dengan struktur 1 digit terakhir isi box [N] diikuti 2 digit nomor box [BB]  -> NBB
c. sort ascending dengan Small
d. ambil nomor box dengan Right 2 karakter
e. mencari posisi (index data) dari hasil proses (d) dalam area data yang exact match entire string
f. ambil nilai data dari area data dengan Index
g. trap error agar error value diganti dengan 0

2. Menghitung total Pcs yang terpilih
=SUMPRODUCT(ABS(-MID(TEXT(B23:K23,"00;;0")&10,3,3)))
>> proses dalam formula :
a. ubah data numerik menjadi 2 digit untuk bilangan positif, dan diisi dengan 0 untuk nilai data 0
b. mengisi paket standar per box adalah 10 pcs
c. mengambil isi box dengan Mid karakter ke 3 sebanyak 3 karakter saja
d. mengubah tipe data menjadi numerik dan mengabsolutkan
e. menjumlahkan hasil proses (d)

File terlampir.

Wassalamu'alaikum Wr. Wb.
Kid.

2012/12/31 Adhi Buzaman <buzamana@yahoo.com>

> 
>Assalaamu'alaikum wr. wb
>
>
>Dear para master excel
>
>
>Mohon pencerahannya untuk kasus yang saya punya, mengurutkan angka lagi dech.... :-)
>
>
>Sebagaimana terlampir dalam file berikut.
>
>
>Matur nuwun ingkang kathah sanget
>
>
>Wassalaamu'alaikum wr.wb
>
>

GROUP FOOTER MESSAGE
---------------------------------------------------------------------
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
---------------------------------------------------------------------
READ MORE....

[smf_addin] Digest Number 2452

8 New Messages

Digest #2452
1a
Re: Getting data from Fundlab by "Randy Harmelink" rharmelink
1b
Re: Getting data from Fundlab by "jcarlosd" jcarlosd
1c
Re: Getting data from Fundlab by "David Nicholas" davidnicholas738
1d
Re: Getting data from Fundlab by "Randy Harmelink" rharmelink
2b
Re: Timed refresh during market hours by "Randy Harmelink" rharmelink
2d
Re: Timed refresh during market hours by "Randy Harmelink" rharmelink

Messages

Sun Dec 30, 2012 7:42 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Your formula works fine for me. So does this:

=RCHGetHTMLTable("https://fundlab.credit-suisse.com/index.cfm?nav=list
","Benchmark",-1,"",1)

Have you gone past the front country-choice screen with the EXCEL Web Query
dialog (preferred) or IE? If you've only done it with something like
FireFox, EXCEL and the add-in won't be able to get to the web page in
question.

Also, note that if you use RCHGetHTMLTable() in VBA, you may need to use
the pDim1 (i.e. rows) and pDim2 (i.e. columns) to tell the function what
size array to return. Otherwise, you get the default value of 10 by 10.

On Sun, Dec 30, 2012 at 3:31 AM, jcarlosd <jcdelrio@procesando.com> wrote:

>
> I don't find anyway to get data from
> https://fundlab.credit-suisse.com/index.cfm?nav=list (it doesn't matter
> what country is selected in the disclaimer page).
>
> It seems stupid, because I see in the page and in the source code the
> table I want below the text "Show performance figures". But when I use a
> standard procedure:
> myURL = "https://fundlab.credit-suisse.com/index.cfm?nav=list"
> myData = RCHGetHTMLTable(myURL, "Show performance figures", 1, "", 1)
>
> There is no content there! I check in the macro code, and at
> iSel1 = InStr(sdata2, UCase(pFind1))
>
> The result is equal to 0, so it doesn't find those words in the page
> source code. It seems that the content of "sdata2" variable is different
> from the actual page. It is strange to me.
>
> What other procedure do you recommend me to use instead of RCHGetHTMLTable?
>

Sun Dec 30, 2012 9:00 am (PST) . Posted by:

"jcarlosd" jcarlosd

It's strange. Before going to that screen I run this code, to overpass the country-code screen (I removed declarations and load waiting to simplify):

URLset = ""https://fundlab.credit-suisse.com/index.cfm?disclaimer=no&change=true"
Set objIE = New InternetExplorer
objIE.Navigate URLset
Set oForm = objIE.Document.forms(0)
On Error Resume Next 'sometimes IE cache does not go to this page
oForm.elements("DOMICILE").Value = "LUX"
oForm.elements("Disclaimer").Value = "YES"
oForm("acceptButton").Click
On Error GoTo 0

'And then I use SMF macro
myURL = "https://fundlab.credit-suisse.com/index.cfm?nav=list"
myData = RCHGetHTMLTable(myURL, "Show performance figures", 1, "", 1)

I use to work just with VBA, not Excel, so I do not how to program the Excel Web query and I work with the Internet Explorer object.

It seems that the page I get with RCHGetHTMLTable is different from the one I can get at that moment with "objIE.Navigate myURL".

If this is true, I do not know how to override it
J. Carlos

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Your formula works fine for me. So does this:
>
> =RCHGetHTMLTable("https://fundlab.credit-suisse.com/index.cfm?nav=list
> ","Benchmark",-1,"",1)
>
> Have you gone past the front country-choice screen with the EXCEL Web Query
> dialog (preferred) or IE? If you've only done it with something like
> FireFox, EXCEL and the add-in won't be able to get to the web page in
> question.
>
> Also, note that if you use RCHGetHTMLTable() in VBA, you may need to use
> the pDim1 (i.e. rows) and pDim2 (i.e. columns) to tell the function what
> size array to return. Otherwise, you get the default value of 10 by 10.
>
> On Sun, Dec 30, 2012 at 3:31 AM, jcarlosd <jcdelrio@...> wrote:
>
> >
> > I don't find anyway to get data from
> > https://fundlab.credit-suisse.com/index.cfm?nav=list (it doesn't matter
> > what country is selected in the disclaimer page).
> >
> > It seems stupid, because I see in the page and in the source code the
> > table I want below the text "Show performance figures". But when I use a
> > standard procedure:
> > myURL = "https://fundlab.credit-suisse.com/index.cfm?nav=list"
> > myData = RCHGetHTMLTable(myURL, "Show performance figures", 1, "", 1)
> >
> > There is no content there! I check in the macro code, and at
> > iSel1 = InStr(sdata2, UCase(pFind1))
> >
> > The result is equal to 0, so it doesn't find those words in the page
> > source code. It seems that the content of "sdata2" variable is different
> > from the actual page. It is strange to me.
> >
> > What other procedure do you recommend me to use instead of RCHGetHTMLTable?
> >
>

Sun Dec 30, 2012 9:50 am (PST) . Posted by:

"David Nicholas" davidnicholas738

You guys know way too much about all of this. Thank heaven and thank you

Sent from my iPad

On Dec 30, 2012, at 11:00 AM, "jcarlosd" <jcdelrio@procesando.com> wrote:

> It's strange. Before going to that screen I run this code, to overpass the country-code screen (I removed declarations and load waiting to simplify):
>
> URLset = ""https://fundlab.credit-suisse.com/index.cfm?disclaimer=no&change=true"
> Set objIE = New InternetExplorer
> objIE.Navigate URLset
> Set oForm = objIE.Document.forms(0)
> On Error Resume Next 'sometimes IE cache does not go to this page
> oForm.elements("DOMICILE").Value = "LUX"
> oForm.elements("Disclaimer").Value = "YES"
> oForm("acceptButton").Click
> On Error GoTo 0
>
> 'And then I use SMF macro
> myURL = "https://fundlab.credit-suisse.com/index.cfm?nav=list"
> myData = RCHGetHTMLTable(myURL, "Show performance figures", 1, "", 1)
>
> I use to work just with VBA, not Excel, so I do not how to program the Excel Web query and I work with the Internet Explorer object.
>
> It seems that the page I get with RCHGetHTMLTable is different from the one I can get at that moment with "objIE.Navigate myURL".
>
> If this is true, I do not know how to override it
> J. Carlos
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
> >
> > Your formula works fine for me. So does this:
> >
> > =RCHGetHTMLTable("https://fundlab.credit-suisse.com/index.cfm?nav=list
> > ","Benchmark",-1,"",1)
> >
> > Have you gone past the front country-choice screen with the EXCEL Web Query
> > dialog (preferred) or IE? If you've only done it with something like
> > FireFox, EXCEL and the add-in won't be able to get to the web page in
> > question.
> >
> > Also, note that if you use RCHGetHTMLTable() in VBA, you may need to use
> > the pDim1 (i.e. rows) and pDim2 (i.e. columns) to tell the function what
> > size array to return. Otherwise, you get the default value of 10 by 10.
> >
> > On Sun, Dec 30, 2012 at 3:31 AM, jcarlosd <jcdelrio@...> wrote:
> >
> > >
> > > I don't find anyway to get data from
> > > https://fundlab.credit-suisse.com/index.cfm?nav=list (it doesn't matter
> > > what country is selected in the disclaimer page).
> > >
> > > It seems stupid, because I see in the page and in the source code the
> > > table I want below the text "Show performance figures". But when I use a
> > > standard procedure:
> > > myURL = "https://fundlab.credit-suisse.com/index.cfm?nav=list"
> > > myData = RCHGetHTMLTable(myURL, "Show performance figures", 1, "", 1)
> > >
> > > There is no content there! I check in the macro code, and at
> > > iSel1 = InStr(sdata2, UCase(pFind1))
> > >
> > > The result is equal to 0, so it doesn't find those words in the page
> > > source code. It seems that the content of "sdata2" variable is different
> > > from the actual page. It is strange to me.
> > >
> > > What other procedure do you recommend me to use instead of RCHGetHTMLTable?
> > >
> >
>
>

Sun Dec 30, 2012 11:40 am (PST) . Posted by:

"Randy Harmelink" rharmelink

What happens after the Click and before the RCHGetHTMLTable()?

On Sun, Dec 30, 2012 at 10:00 AM, jcarlosd <jcdelrio@procesando.com> wrote:

> It's strange. Before going to that screen I run this code, to overpass the
> country-code screen (I removed declarations and load waiting to simplify):
>
> URLset = ""
> https://fundlab.credit-suisse.com/index.cfm?disclaimer=no&change=true"
> Set objIE = New InternetExplorer
> objIE.Navigate URLset
> Set oForm = objIE.Document.forms(0)
> On Error Resume Next 'sometimes IE cache does not go to this page
> oForm.elements("DOMICILE").Value = "LUX"
> oForm.elements("Disclaimer").Value = "YES"
> oForm("acceptButton").Click
> On Error GoTo 0
>
> 'And then I use SMF macro
> myURL = "https://fundlab.credit-suisse.com/index.cfm?nav=list"
> myData = RCHGetHTMLTable(myURL, "Show performance figures", 1, "", 1)
>
> I use to work just with VBA, not Excel, so I do not how to program the
> Excel Web query and I work with the Internet Explorer object.
>
> It seems that the page I get with RCHGetHTMLTable is different from the
> one I can get at that moment with "objIE.Navigate myURL".
>
> If this is true, I do not know how to override it
>

Sun Dec 30, 2012 3:23 pm (PST) . Posted by:

"rho49m" rho49m

Really have no idea what I am doing, basically I would like to download stock data from Yahoo every 10 minutes during market hours, would like to set start and end times with a refresh interval. I assume it has already been done but did not find the post. I am none too smart so need an explicit guide. Thanks

Sun Dec 30, 2012 4:01 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Take a look at this file:

RCHGetYahooQuotes-Example-Timed-Update.xls

On Sun, Dec 30, 2012 at 4:13 PM, rho49m <rho49m@yahoo.it> wrote:

> Really have no idea what I am doing, basically I would like to download
> stock data from Yahoo every 10 minutes during market hours, would like to
> set start and end times with a refresh interval. I assume it has already
> been done but did not find the post. I am none too smart so need an
> explicit guide. Thanks
>

Mon Dec 31, 2012 2:56 am (PST) . Posted by:

"rho49m" rho49m

OK- but where do I find "RCHGetYahooQuotes-Example-Timed-Update.xls"?
Tried your help file, Google search, site search but did not find it! Feeling pretty silly...

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Take a look at this file:
>
> RCHGetYahooQuotes-Example-Timed-Update.xls
>
> On Sun, Dec 30, 2012 at 4:13 PM, rho49m <rho49m@...> wrote:
>
> > Really have no idea what I am doing, basically I would like to download
> > stock data from Yahoo every 10 minutes during market hours, would like to
> > set start and end times with a refresh interval. I assume it has already
> > been done but did not find the post. I am none too smart so need an
> > explicit guide. Thanks
> >
>

Mon Dec 31, 2012 3:04 am (PST) . Posted by:

"Randy Harmelink" rharmelink

In the FILES area of the group:

Files > Templates and Examples > RCHGetYahooQuotes

http://finance.groups.yahoo.com/group/smf_addin/files/Templates%20and%20Examples/RCHGetYahooQuotes/

On Mon, Dec 31, 2012 at 3:56 AM, rho49m <rho49m@yahoo.it> wrote:

> OK- but where do I find "RCHGetYahooQuotes-Example-Timed-Update.xls"?
> Tried your help file, Google search, site search but did not find it!
> Feeling pretty silly...
>
READ MORE....