Hot Fox: Re: ]] XL-mania [[ sort vlookup beberapa kolom

terça-feira, 26 de junho de 2012

Re: ]] XL-mania [[ sort vlookup beberapa kolom

Sudah tiga hari belum ada yang bantu.

Dengan sedikit kerja keras (walupun belum tuntas) saya coba bantu dengan Macro berikut ini (file terlampir), karena kalau pakai formula harus ada sheet bantu dan harus disortir lagi

Sub Tes()

Dim Rng1 As Range

Dim Rng2 As Range

Dim Rng3 As Range

Dim i As Long

Dim irow As Long

Set Rng1 = Sheets("Kas").Range("A4").CurrentRegion

Set Rng1 = Rng1.Offset(3, 0).Resize(Rng1.Rows.Count - 3)

Set Rng2 = Sheets("Bank").Range("A4").CurrentRegion

Set Rng2 = Rng2.Offset(3, 0).Resize(Rng2.Rows.Count - 3)

Set Rng3 = Sheets("Memorial").Range("A3").CurrentRegion

Set Rng3 = Rng3.Offset(2, 0).Resize(Rng3.Rows.Count - 2)

Set Rng4 = Sheets("GL").Range("A3").CurrentRegion

Set Rng4 = Rng4.Offset(2, 0).Resize(Rng4.Rows.Count - 2)

Rng4.ClearContents

irow = i + 3

With Sheets("Kas")

For i = 1 To Rng1.Rows.Count

If Sheets("GL").Range("G1") = Rng1(i, 4) Then

Sheets("GL").Cells(irow, 1) = Rng1(i, 1)

Sheets("GL").Cells(irow, 1).NumberFormat = "[$-409]d-mmm-yy;@"

Sheets("GL").Cells(irow, 2) = Rng1(i, 2)

Sheets("GL").Cells(irow, 3) = Rng1(i, 3)

Sheets("GL").Cells(irow, 4) = Sheets("Kas").Range("D1")

Sheets("GL").Cells(irow, 5) = Rng1(i, 6)

Sheets("GL").Cells(irow, 5).NumberFormat = "#,##0"

Sheets("GL").Cells(irow, 6) = Rng1(i, 5)

Sheets("GL").Cells(irow, 6).NumberFormat = "#,##0"

Sheets("GL").Cells(irow, 7).FormulaR1C1 = "=SUM(R3C5:RC[-2])-SUM(R3C6:RC[-1])"

Sheets("GL").Cells(irow, 7).NumberFormat = "#,##0"

irow = irow + 1

End If

Next i

End With

With Sheets("Bank")

For i = 1 To Rng2.Rows.Count

If Sheets("GL").Range("G1") = Rng2(i, 4) Then

Sheets("GL").Cells(irow, 1) = Rng2(i, 1)

Sheets("GL").Cells(irow, 1).NumberFormat = "[$-409]d-mmm-yy;@"

Sheets("GL").Cells(irow, 2) = Rng2(i, 2)

Sheets("GL").Cells(irow, 3) = Rng2(i, 3)

Sheets("GL").Cells(irow, 4) = Sheets("Bank").Range("D1")

Sheets("GL").Cells(irow, 5) = Rng2(i, 6)

Sheets("GL").Cells(irow, 5).NumberFormat = "#,##0"

Sheets("GL").Cells(irow, 6) = Rng2(i, 5)

Sheets("GL").Cells(irow, 6).NumberFormat = "#,##0"

Sheets("GL").Cells(irow, 7).FormulaR1C1 = "=SUM(R3C5:RC[-2])-SUM(R3C6:RC[-1])"

Sheets("GL").Cells(irow, 7).NumberFormat = "#,##0"

irow = irow + 1

End If

Next i

End With

With Sheets("Memorial")

For i = 1 To Rng3.Rows.Count

If Sheets("GL").Range("G1") = Rng3(i, 4) Then

Sheets("GL").Cells(irow, 1) = Rng3(i, 1)

Sheets("GL").Cells(irow, 1).NumberFormat = "[$-409]d-mmm-yy;@"

Sheets("GL").Cells(irow, 2) = Rng3(i, 2)

Sheets("GL").Cells(irow, 3) = Rng3(i, 3)

Sheets("GL").Cells(irow, 4) = "Memorial"

Sheets("GL").Cells(irow, 5) = Rng3(i, 5)

Sheets("GL").Cells(irow, 5).NumberFormat = "#,##0"

Sheets("GL").Cells(irow, 6) = Rng3(i, 6)

Sheets("GL").Cells(irow, 6).NumberFormat = "#,##0"

Sheets("GL").Cells(irow, 7).FormulaR1C1 = "=SUM(R3C5:RC[-2])-SUM(R3C6:RC[-1])"

Sheets("GL").Cells(irow, 7).NumberFormat = "#,##0"

irow = irow + 1

End If

Next i

End With

End Sub

Tahapannya adalah sbb :

- Deklarasi Variable dengan Dimension (DIM)

- Setting Range untuk masing2 Sheet (SET)

- Looping untuk mengambil data dari masing2 Sheet (WITH untuk Sheet, FOR untuk Looping, IF untuk penentuan Kriteria),

kemudian menenpatkannya pada Sheet GL (satu sheet cukup).

Semoga membantu.

Salam,

Hendrik Karnadi

saya pakai Excel 2003 supaya lebih banyak yang dapat menggunakan file tersebut

From: ardani k <ardani_k@yahoo.com>
To: blajar excel <XL-mania@yahoogroups.com>
Sent: Saturday, 23 June 2012, 5:50
Subject: ]] XL-mania [[ sort vlookup beberapa kolom

dear all mohon bantuanya:

1. gmn cara vlookup dr sheet kas,bank,memo ke kode 521...tsb.

2. gmn sort per kolomnya disheet 521...agar tidak ada baris yg kosong

fyi...
Thanks
best regards,

Danz

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (2)

Recent Activity:

New Members 51

Visit Your Group

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Woooowwww... member XL-mania tembus 20,000!!!                     |
+-------------------------------------------------------------------+
| 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.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              |
+-------------------------------------------------------------------+

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

__,_._,___

Nenhum comentário:

Postar um comentário

Hot Fox

terça-feira, 26 de junho de 2012

Re: ]] XL-mania [[ sort vlookup beberapa kolom

Nenhum comentário:

Arquivo do blog