]] XL-mania [[ Digest Number 2330

Microsoft Excel Community

Messages In This Digest (10 Messages)

1a.

MEMBUAT BORDERS  OTOMATIS From: Rama

1b.

Re: MEMBUAT BORDERS OTOMATIS From: i Haps

1c.

Re: MEMBUAT BORDERS  OTOMATIS From: nangagus

1d.

Re: MEMBUAT BORDERS  OTOMATIS From: nangagus

1e.

Re: MEMBUAT BORDERS  OTOMATIS From: hendrik karnadi

2a.

Re: Bagaimana Cara Vlookup Tetapi Dengan Satu Reference Dengan Lebih From: Abu_fattah

3.

Runtime error '1004': From: Ecko Ps

4.

putar grafik From: Rahadian Wibowo

5a.

input data dari per kolom jadi per baris From: ahmad nur ramdani usman

5b.

Re: input data dari per kolom jadi per baris From: Tommy

View All Topics | Create New Topic

Messages

1a.

MEMBUAT BORDERS  OTOMATIS

Posted by: "Rama" rama@gudangmas.com   ramawibowo_lp3i

Thu Feb 2, 2012 7:23 am (PST)

Dear Xl mania,

Saya memiliki program sederhana VB dengan report excel.

Dari hasil koding di bawah akan menhasilkan data seperti file terlampir
(Sheet 1).

Yang saya mau tanyakan adalah Bagaimana caranya agar saat print, range dari
data tersebut memiliki border secara otomatis (Sheet 2).

Ada yg bisa tambahkan coding dibawah, agar hasilnya bisa seperti yg di
inginkan ?

If Not ramadhan.EOF Then

.Sheets("reguler").Select

a = 2

Do While Not ramadhan.EOF

.Cells(a, 1) = ramadhan.Fields("cek")

.Cells(a, 2) = ramadhan.Fields("kode_customer")

.Cells(a, 3) = ramadhan("customer_nama")

.Cells(a, 4) = ramadhan("no_invoice")

.Cells(a, 5) = ramadhan("no_faktur")

.Cells(a, 6) = ramadhan("tgl_invoice")

.Cells(a, 7) = ramadhan("tgl_input")

.Cells(a, 8) = ramadhan("kode_item")

.Cells(a, 9) = Val(ramadhan("qty"))

.Cells(a, 10) = Val(ramadhan("harga_beli"))

.Cells(a, 11) = Val(ramadhan("harga_jual"))

.Cells(a, 12) = (ramadhan("qty")) * (ramadhan("harga_beli"))

.Cells(a, 13) = (ramadhan("qty")) * (ramadhan("harga_jual"))

.Cells(a, 14) = (.Cells(a, 13)) - (.Cells(a, 12))

.Cells(a, 15) = ramadhan("remark")

ramadhan.MoveNext

a = a + 1

Loop

Else

End If

Terima kasih

Ramadhan

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

1b.

Re: MEMBUAT BORDERS OTOMATIS

Posted by: "i Haps" hapsari.stlizbeth@gmail.com   liz_indri_haps

Fri Feb 3, 2012 4:06 am (PST)

mungkin menjadi spt ini

If Not ramadhan.EOF Then****

.Sheets("reguler").Select****

a = 2****

Do While Not ramadhan.EOF

.Cells(a, 1) = ramadhan.Fields("cek")

.Cells(a, 2) = ramadhan.Fields("kode_customer")****

.Cells(a, 3) = ramadhan("customer_nama")****

.Cells(a, 4) = ramadhan("no_invoice")****

.Cells(a, 5) = ramadhan("no_faktur")****

.Cells(a, 6) = ramadhan("tgl_invoice")****

.Cells(a, 7) = ramadhan("tgl_input")****

.Cells(a, 8) = ramadhan("kode_item")****

.Cells(a, 9) = Val(ramadhan("qty"))****

.Cells(a, 10) = Val(ramadhan("harga_beli"))****

.Cells(a, 11) = Val(ramadhan("harga_jual"))****

.Cells(a, 12) = (ramadhan("qty")) * (ramadhan("harga_beli"))****

.Cells(a, 13) = (ramadhan("qty")) * (ramadhan("harga_jual"))****

.Cells(a, 14) = (.Cells(a, 13)) - (.Cells(a, 12))****

.Cells(a, 15) = ramadhan("remark")****

'----tambahan----

*With Range(.cell(a, 1), .cell(a, 15))*

.Interior.Color = 65535

With .Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With .Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With .Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With .Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With .Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

* End With*
* '----end of tambahan ----*

ramadhan.MoveNext****

a = a + 1****

Loop

Else****

** **

End If
'----

- i -

2012/2/2 Rama <rama@gudangmas.com>
>
> Dear Xl mania,
> Saya memiliki program sederhana VB dengan report excel.
> Dari hasil koding di bawah akan menhasilkan data seperti file terlampir
(Sheet 1).
> Yang saya mau tanyakan adalah Bagaimana caranya agar saat print, range
dari data tersebut memiliki border secara otomatis (Sheet 2).
> Ada yg bisa tambahkan coding dibawah, agar hasilnya bisa seperti yg di
inginkan ?
>
> If Not ramadhan.EOF Then
> .Sheets("reguler").Select
> a = 2
> Do While Not ramadhan.EOF
>
> .Cells(a, 1) = ramadhan.Fields("cek")
> .Cells(a, 2) = ramadhan.Fields("kode_customer")
> .Cells(a, 3) = ramadhan("customer_nama")
> .Cells(a, 4) = ramadhan("no_invoice")
> .Cells(a, 5) = ramadhan("no_faktur")
> .Cells(a, 6) = ramadhan("tgl_invoice")
> .Cells(a, 7) = ramadhan("tgl_input")
> .Cells(a, 8) = ramadhan("kode_item")
> .Cells(a, 9) = Val(ramadhan("qty"))
> .Cells(a, 10) = Val(ramadhan("harga_beli"))
> .Cells(a, 11) = Val(ramadhan("harga_jual"))
> .Cells(a, 12) = (ramadhan("qty")) * (ramadhan("harga_beli"))
> .Cells(a, 13) = (ramadhan("qty")) * (ramadhan("harga_jual"))
> .Cells(a, 14) = (.Cells(a, 13)) - (.Cells(a, 12))
> .Cells(a, 15) = ramadhan("remark")
>
> ramadhan.MoveNext
> a = a + 1
> Loop
> Else
> End If
>
> Terima kasih
> Ramadhan

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

1c.

Re: MEMBUAT BORDERS  OTOMATIS

Posted by: "nangagus" nangagus@gmail.com

Fri Feb 3, 2012 4:06 am (PST)

Bagaimana kalau dicoba menggunakan ini:
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
On 02/02/2012 08:51, Rama wrote:
>
> Dear Xl mania,
>
> Saya memiliki program sederhana VB dengan report excel.
>
> Dari hasil koding di bawah akan menhasilkan data seperti file
> terlampir (Sheet 1).
>
> Yang saya mau tanyakan adalah Bagaimana caranya agar saat print,
> range dari data tersebut memiliki border secara otomatis (Sheet 2).
>
> Ada yg bisa tambahkan coding dibawah, agar hasilnya bisa seperti yg di
> inginkan ?
>
> If Not ramadhan.EOF Then
>
> .Sheets("reguler").Select
>
> a = 2
>
> Do While Not ramadhan.EOF
>
> .Cells(a, 1) = ramadhan.Fields("cek")
>
> .Cells(a, 2) = ramadhan.Fields("kode_customer")
>
> .Cells(a, 3) = ramadhan("customer_nama")
>
> .Cells(a, 4) = ramadhan("no_invoice")
>
> .Cells(a, 5) = ramadhan("no_faktur")
>
> .Cells(a, 6) = ramadhan("tgl_invoice")
>
> .Cells(a, 7) = ramadhan("tgl_input")
>
> .Cells(a, 8) = ramadhan("kode_item")
>
> .Cells(a, 9) = Val(ramadhan("qty"))
>
> .Cells(a, 10) = Val(ramadhan("harga_beli"))
>
> .Cells(a, 11) = Val(ramadhan("harga_jual"))
>
> .Cells(a, 12) = (ramadhan("qty")) *
> (ramadhan("harga_beli"))
>
> .Cells(a, 13) = (ramadhan("qty")) *
> (ramadhan("harga_jual"))
>
> .Cells(a, 14) = (.Cells(a, 13)) - (.Cells(a, 12))
>
> .Cells(a, 15) = ramadhan("remark")
>
> ramadhan.MoveNext
>
> a = a + 1
>
> Loop
>
> Else
>
> End If
>
> Terima kasih
>
> Ramadhan
>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

1d.

Re: MEMBUAT BORDERS  OTOMATIS

Posted by: "nangagus" nangagus@gmail.com

Fri Feb 3, 2012 4:06 am (PST)

Coba gunakan:
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

On 02/02/2012 08:51, Rama wrote:
>
> Dear Xl mania,
>
> Saya memiliki program sederhana VB dengan report excel.
>
> Dari hasil koding di bawah akan menhasilkan data seperti file
> terlampir (Sheet 1).
>
> Yang saya mau tanyakan adalah Bagaimana caranya agar saat print,
> range dari data tersebut memiliki border secara otomatis (Sheet 2).
>
> Ada yg bisa tambahkan coding dibawah, agar hasilnya bisa seperti yg di
> inginkan ?
>
> If Not ramadhan.EOF Then
>
> .Sheets("reguler").Select
>
> a = 2
>
> Do While Not ramadhan.EOF
>
> .Cells(a, 1) = ramadhan.Fields("cek")
>
> .Cells(a, 2) = ramadhan.Fields("kode_customer")
>
> .Cells(a, 3) = ramadhan("customer_nama")
>
> .Cells(a, 4) = ramadhan("no_invoice")
>
> .Cells(a, 5) = ramadhan("no_faktur")
>
> .Cells(a, 6) = ramadhan("tgl_invoice")
>
> .Cells(a, 7) = ramadhan("tgl_input")
>
> .Cells(a, 8) = ramadhan("kode_item")
>
> .Cells(a, 9) = Val(ramadhan("qty"))
>
> .Cells(a, 10) = Val(ramadhan("harga_beli"))
>
> .Cells(a, 11) = Val(ramadhan("harga_jual"))
>
> .Cells(a, 12) = (ramadhan("qty")) *
> (ramadhan("harga_beli"))
>
> .Cells(a, 13) = (ramadhan("qty")) *
> (ramadhan("harga_jual"))
>
> .Cells(a, 14) = (.Cells(a, 13)) - (.Cells(a, 12))
>
> .Cells(a, 15) = ramadhan("remark")
>
> ramadhan.MoveNext
>
> a = a + 1
>
> Loop
>
> Else
>
> End If
>
> Terima kasih
>
> Ramadhan
>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

1e.

Re: MEMBUAT BORDERS  OTOMATIS

Posted by: "hendrik karnadi" hendrikkarnadi@yahoo.com   hendrikkarnadi

Fri Feb 3, 2012 4:06 am (PST)

Pak Rama,
Terlampir Macro hasil rekaman.

Salam,
Hendrik Karnadi

________________________________
From: Rama <rama@gudangmas.com>
To: XL-mania@yahoogroups.com
Sent: Thursday, 2 February 2012, 8:51
Subject: ]] XL-mania [[ MEMBUAT BORDERS OTOMATIS


 
Dear Xl mania,
 
Saya memiliki program sederhana VB dengan report excel.
Dari hasil koding di bawah akan menhasilkan data seperti file terlampir (Sheet 1).
Yang saya mau tanyakan adalah Bagaimana caranya agar saat print,  range dari data tersebut memiliki border secara otomatis (Sheet 2).
Ada yg bisa tambahkan coding dibawah, agar hasilnya bisa seperti yg di inginkan ?
 
 
 
 
    If Not ramadhan.EOF Then
    .Sheets("reguler").Select
    a = 2
 
                Do While Not ramadhan.EOF
               
                .Cells(a, 1) = ramadhan.Fields("cek")
                .Cells(a, 2) = ramadhan.Fields("kode_customer")
                .Cells(a, 3) = ramadhan("customer_nama")
                .Cells(a, 4) = ramadhan("no_invoice")
                .Cells(a, 5) = ramadhan("no_faktur")
                .Cells(a, 6) = ramadhan("tgl_invoice")
                .Cells(a, 7) = ramadhan("tgl_input")
                .Cells(a, 8) = ramadhan("kode_item")
                .Cells(a, 9) = Val(ramadhan("qty"))
                .Cells(a, 10) = Val(ramadhan("harga_beli"))
                .Cells(a, 11) = Val(ramadhan("harga_jual"))
                .Cells(a, 12) = (ramadhan("qty")) * (ramadhan("harga_beli"))
                .Cells(a, 13) = (ramadhan("qty")) * (ramadhan("harga_jual"))
                .Cells(a, 14) = (.Cells(a, 13)) - (.Cells(a, 12))
                .Cells(a, 15) = ramadhan("remark")
 
    ramadhan.MoveNext
    a = a + 1
   
    Loop
    Else
 
    End If
 
 
 
Terima kasih
Ramadhan

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

2a.

Re: Bagaimana Cara Vlookup Tetapi Dengan Satu Reference Dengan Lebih

Posted by: "Abu_fattah" abu_fattah_01@yahoo.co.id   abu_fattah_01

Thu Feb 2, 2012 7:23 am (PST)

Ini ada formula,..mungkin membantu,….

{=VLOOKUP(C12,IF($H$5:H6=D12,$F$5:H6,""),2,FALSE)}

Coba lagi ya,..

Iyon

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)

3.

Runtime error '1004':

Posted by: "Ecko Ps" eckops@ymail.com   eckops@ymail.com

Thu Feb 2, 2012 7:25 am (PST)

Para master, mau tanya donk..
Saya punya problem nih, mengenai macro excel.

Saya buat sebuah program macro kecil pake VB6, lalu di generate menjadi file Windows .dll. Nah, saya juga punya file excel yang manggil fungsi2 di file .dll itu..

Saat saya menggunakan Excel 2010, macro saya dapat berjalan dengan baik tanpa masalah.
Nah, pada saat file excel itu dibuka di Excel 2007, terjadi error. Tepatnya pada saat saya mengklik radio button/option button.
Error nya seperti ini :

Runtime error '1004':
Unable to get the OptionButtons property of the worksheet class

Kecurigaan saya, karena Optionbutton 2007 dan 2010 tidak kompatible.
Mohon bantuannya para master, bagaimana solusinya agar macro nya berjalan dengan baik di semua versi Excel.

Terima Kasih.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)

4.

putar grafik

Posted by: "Rahadian Wibowo" rahadianwibowo@yahoo.com   rahadianwibowo

Thu Feb 2, 2012 7:31 am (PST)

dear masters excel

saya masih bingung perihal cara memutar grafik meski sudah saya switch row to column yang saya inginkan grafik tampak vertikal bukan horisontal, mohon bantuannya, grafik saya attach, terima kasih sebelumnya

jabat erat

bow wow

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)

5a.

input data dari per kolom jadi per baris

Posted by: "ahmad nur ramdani usman" anru_1988@yahoo.co.id   anru_1988

Thu Feb 2, 2012 7:33 am (PST)

Dear para master XL,,

mohon bantuannya para master, dalam pengerjaan report saya, karena selama ini saya menginput data secara manual, dan itu sangat menyita waktu karena saya harus menginput dari data mentah di lapangan kemudian dijadikan per week dan di tambah lg menjadi per cont, selanjutnya dibuat grafik per item sold, Dev, dll per cont masing-masing. waktu input data banyak tp waktu analisis menjadi sedikit.

kolom tersebut akan direkap kembali menjadi per kolom count agar bisa dibandingkan per week nya di sheet terpisah
bagaimana untuk memudahkan pemindahan data dari per wilayah tersebut menjadi per cont tersebut.
mohon sekali
bantuannya para master XL

terimakasih sebelumnya

dani.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

5b.

Re: input data dari per kolom jadi per baris

Posted by: "Tommy" tomz.zlow@gmail.com   tomz_aja

Fri Feb 3, 2012 4:06 am (PST)

Dear Pak Ahmad,

Mungkin file terlampir bisa membantu mempersingkat waktu. Dengan sedikit modifikasi tabel dan menggunakan kolom/baris bantuan (yang dapat di-hide).

Blessings,
tomz

----- Original Message -----
From: ahmad nur ramdani usman
To: xl mania
Sent: Monday, January 30, 2012 5:09 PM
Subject: ]] XL-mania [[ input data dari per kolom jadi per baris

Dear para master XL,,

mohon bantuannya para master, dalam pengerjaan report saya, karena selama ini saya menginput data secara manual, dan itu sangat menyita waktu karena saya harus menginput dari data mentah di lapangan kemudian dijadikan per week dan di tambah lg menjadi per cont, selanjutnya dibuat grafik per item sold, Dev, dll per cont masing-masing. waktu input data banyak tp waktu analisis menjadi sedikit.

kolom tersebut akan direkap kembali menjadi per kolom count agar bisa dibandingkan per week nya di sheet terpisah
bagaimana untuk memudahkan pemindahan data dari per wilayah tersebut menjadi per cont tersebut.
mohon sekali bantuannya para master XL

terimakasih sebelumnya

dani.

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)

Recent Activity

•  74
  New Members

Visit Your Group

Cat Groups

on Yahoo! Groups

Share pictures &

stories about cats.

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! Groups

Mental Health Zone

Bi-polar disorder

Find support

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

Messages

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| ayooo... coba cari XL-mania di linkedin.com                       |
| Gong Xi Fat Choi! Mana angpaonya... wkwkwkwkwk...                 |
+-------------------------------------------------------------------+
| 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              |
+-------------------------------------------------------------------+

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe