Tecnologia, programação e muito Visual FoxPro.

quarta-feira, 22 de dezembro de 2010

calculated field di pivot table was >>>RE: ]] XL-mania [[ Error Macro Sumproduct

 

 

Bisa nggak saat menggunakan calculated field di pivot table, formulanya menggunakan user defined function (UDF)?

 

Thanks

-zainul-

 


From: XL-mania@yahoogroups.com [mailto:XL-mania@yahoogroups.com] On Behalf Of anton suryadi
Sent: Wednesday, December 22, 2010 10:49 PM
To: XL-mania@yahoogroups.com
Subject: Re: ]] XL-mania [[ Error Macro Sumproduct

 

 

Sebelumnya, jika belum, pertimbangkan penggunaan Pivot Table,, senjata utama MS Excel sebagai sebuah "Business Intelligence Tool".

 

Dengan sedikit perubahan struktur tabel data, kita dapat menjadikan hasil PivotTable semakin mendekati keinginan. 

Lihat file terlampir.  

 

Namun, jika ingin tetap menggunakan macro, silakan dicoba code yang berikut ini:

 

Sub sumproduct_to_range_value()

    'anton suryadi 22/Des/2010

    Dim x As Range

    Set x = Range("A1").CurrentRegion

    Set x = x.Offset(2, 1).Resize(x.Rows.Count - 3, 2)

    Set p = Sheets("DB").Range("A1").CurrentRegion

    Set A = p.Offset(1, 0).Resize(p.Rows.Count - 1, 1)

    Set B = p.Offset(1, 1).Resize(p.Rows.Count - 1, 1)

    Set C = p.Offset(1, 2).Resize(p.Rows.Count - 1, 1)

    x.Value = "=SUMPRODUCT((DB!" & A.Address & "=$A3)*(DB!" & B.Address & "=B$2)*(DB!" & C.Address & "))"

    x.Value = x.Value

End Sub

 

File terlampir

 

 

>semoga bermanfaat

 

 

Substance Over Form, Substance Over Style,,

 

 

 


From: uharyanto <uharyanto@gmail.com>
To: XL-mania@yahoogroups.com
Sent: Tue, December 21, 2010 10:12:46 AM
Subject: ]] XL-mania [[ Error Macro Sumproduct

 

Dear master2 excel,,,

Saya mencoba menbuat code macro untuk merekap hasil dari sheet lain
akan tetapi setiap saya jalankan selalu error ("type mismatch"), saya
telah coba berulang - ulang tapi tetap saja gagal.

Sub Rekap()
Dim HslD, HslC As Range
Dim A, B, C, D, E, F As Range

Set HslD = Sheets("REKAP").Range("A3").CurrentRegion
Set HslD = HslD.Offset(2, 1).Resize(HslD.Rows.Count - 3, 1)
Set HslC = HslD.Offset(0, 1)
Set A = HslD.Offset(0, -1)
Set C = Range("B2")
Set E = Range("C2")

Sheets("DB").Select
Set B = Sheets("DB").Range("A2").CurrentRegion
Set B = B.Offset(1, 0).Resize(B.Rows.Count - 1, 1)
Set D = B.Offset(0, 1)
Set F = B.Offset(0, 2)

Sheets("REKAP").Select
HslD.Value = WorksheetFunction.SumProduct((A = B) * (C = D) * (F))
End Sub

Kiranya para rekan dapat memberi saran dan masukkan, apa penyebab dan
apa yang harus saya lakukan supaya code tersebut bisa berjalan dengan
baik.

--
Kind Regard
Ujank Haryanto

 

__._,_.___
Recent Activity:
+-:: XL-mania ::::::::::::::::::::----------------------------------+
| sOrRy dOrRy j3K... m0m0dS LaGi HeC-tiC... waQaQaQa...             |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf          |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message,  melecehkan,  tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan  winzip  jika  perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke  |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau...  |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id]  |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+
MARKETPLACE

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


Hobbies & Activities Zone: Find others who share your passions! Explore new interests.


Find useful articles and helpful tips on living with Fibromyalgia. Visit the Fibromyalgia Zone today!

.

__,_._,___

Nenhum comentário:

Arquivo do blog