Hot Fox: Re: ]] XL-mania [[ Re: Diurut

om om om tinggal dipanggil aja oom.....Kalo Dari ....
- Kecil ke Besar manggilnya kayak gini....
Call SortingAlamatIP(Ascending)
- Besar Ke Kecil
Call SortingAlamatIP(Descending)

kodingannya oom ... :
' --------------------------------------------------------------------
' Nama Modul :ModSortasi.mod
' Kegunaan : Mensortasi Dalam Bentuk Array
' Created By : puthut_vai@yahoo.com @ 28 Okt 2009
' --------------------------------------------------------------------

Public Enum EnumPilihan
    Ascending = 1
    Descending = 0
End Enum

Sub SortingAlamatIP(ReturnType As EnumPilihan)
Dim Arr() As String ' Array Dinamis
    Dim wShitHasil, InputSheet As Worksheet
    Dim vRange As Range
    Dim vCountBaris As Long
    Dim BarisTerakhir As Long
    Dim vSementara
    Dim vHasilSorting As String
    Dim vRangePencarian As Range
    Dim sPencarian As String


    Application.ScreenUpdating = False
    Set InputSheet = Worksheets("INPUT SHEET") ' Set Input

    ' Baris terakhir pada wShit Input
    BarisTerakhir = InputSheet.Range("A" & Rows.Count).End(xlUp).Row

    ' Deklarasi ulang Array dari Dinamis Menjadi Statis
    ' Berdasarkan Jumlah Baris input Shit
    ReDim Arr(1 To BarisTerakhir)

    InputSheet.Activate

    vCountBaris = 1

'   Looping ampe habis
    Do While Cells(vCountBaris, 1) <> ""
        vSementara = Trim(Replace(Cells(vCountBaris, 1), ".", ""))
        ' Insert Text Pada Cell ke dalam array
        Arr(vCountBaris) = Right(vSementara, Len(vSementara) - 8)
        vCountBaris = vCountBaris + 1
    Loop

    ' Buat Shit Baru
    Set wShitHasil = ThisWorkbook.Worksheets.Add

     ' Masukan Hasil Array ke Sheet Baru
    Set vRange = wShitHasil.Range("A1").Resize(UBound(Arr) - LBound(Arr) + 1, 1)
    vRange = Application.Transpose(Arr)

    ' Pilihan Sortasi Array yang dipilih pada saat dipanggil
    Select Case ReturnType
        ' Ascending (Kecil ke Besar)
        Case Ascending
            vRange.Sort key1:=vRange, order1:=xlAscending, MatchCase:=False
        ' Descending (Besar ke Kecil)
        Case Descending
          vRange.Sort key1:=vRange, order1:=xlDescending, MatchCase:=False
    End Select


    For vCountBaris = 1 To vRange.Rows.Count

       vSementara = Trim(Replace(InputSheet.Cells(vCountBaris, 1), ".", ""))
       vHasilSorting = wShitHasil.Cells(vCountBaris, 1)

    ' Set Pencarian : 192.168.11. + Variabel Hasil Sorting
    ' Masih pake koding Brutal (Brute Force)
    ' Dirubah aja oom....saya gak tau bgmn skema penomoran IP Address
    ' Punya si omm...
    sPencarian = "192.168.11." & vHasilSorting

   ' Pergantian Variabel Hasil Sorting dengan bentuk IP addreess sebenarnya
   With InputSheet.Range("A1:A" & BarisTerakhir)
        Set vRangePencarian = .Find(What:=sPencarian, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)

        wShitHasil.Cells(vCountBaris, 1) = vRangePencarian
    End With
    Next vCountBaris
    Application.ScreenUpdating = True
    Call RapatkanBarisan
End Sub

' --------------------------------------------------------------------
' Nama Modul :ModRapatkanBarisan.mod
' Kegunaan : Autofit cell dalam satu workshit
' Created By : puthut_vai@yahoo.com @ 28 Okt 2009
' --------------------------------------------------------------------
Option Explicit

Public Sub RapatkanBarisan()
    If ActiveWorkbook Is Nothing Then Exit Sub
    Dim i#
    If ActiveWindow.SelectedSheets.Count > 1 Then
        For i = 1 To ActiveWindow.SelectedSheets.Count
            ActiveWindow.SelectedSheets(i).Cells.EntireColumn.AutoFit
        Next
    Else
        Cells.EntireColumn.AutoFit
    End If
End Sub

File Terlampir oom.....
- ^ Puthut Wibowo ^ - || puthut_vai@yahoo.com

--- On Wed, 10/28/09, { J 4 j a N k } <jajank@gmail.com> wrote:

From: { J 4 j a N k } <jajank@gmail.com>
Subject: ]] XL-mania [[ Re: Diurut
To: XL-mania@yahoogroups.com
Date: Wednesday, October 28, 2009, 2:30 PM

Mohon maaf hasil yang diinginkan adalah :

192.168.11.1

192.168.11.12

192.168.11.15

192.168.11.100

192.168.11.110

192.168.11.139

192.168.11.141

192.168.11.144

192.168.11.145

192.168.11.146

192.168.11.148

192.168.11.149

192.168.11.150

192.168.11.151

192.168.11.152

192.168.11.153

192.168.11.154

192.168.11.155

192.168.11.156

192.168.11.157

dan sebaliknya

Thanks,
J@j@nk

____________ _________ _________ _________ _________ _________ _________ _________ _________ ________

Misal data IP yang akan dishorting terdapat dikolom A
Formula di kolom B misal:
=MID(SUBSTITUTE( A2,".",""),9,3)
Begitu juga baris yang lainnya

Setelah itu short seperti biasa untuk kolom B

>semoga bermanfaat

From: { J 4 j a N k } <jajank@gmail. com>
To: XL-mania@yahoogroup s.com
Sent: Tue, October 27, 2009 1:44:37 PM
Subject: ]] XL-mania [[ Diurut

Mohon bantuan untuk mensort data IP dari angka terendah ke tertinggi dan sebaliknya :
192.168.11.1
192.168.11.100
192.168.11.110
192.168.11.12
192.168.11.139
192.168.11.141
192.168.11.144
192.168.11.145
192.168.11.146
192.168.11.148
192.168.11.149
192.168.11.15
192.168.11.150
192.168.11.151
192.168.11.152
192.168.11.153
192.168.11.154
192.168.11.155
192.168.11.156
192.168.11.157
Thanks,
J4j@nK

__._,_.___

Messages in this topic (11) Reply (via web post) | Start a new topic

Messages

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Moderasi akan lambat minggu ini, momods pergi ke tempat jauh      |
| 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              |
+-------------------------------------------------------------------+

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

__,_._,___

Hot Fox

quarta-feira, 28 de outubro de 2009

Re: ]] XL-mania [[ Re: Diurut

Nenhum comentário:

Arquivo do blog