Excel VBA: สำหรับการตั้งค่าแต่ละวง

นี่อาจไม่ใช่คำตอบที่คุณกำลังมองหา แต่ฉันคิดว่านี่คือวิธีที่ฉันจะเข้าใกล้โครงการของคุณ การดูข้อมูลดิบที่คุณได้รับในรายงานและวางลงในสเปรดชีตจะเป็นประโยชน์

สมมติฐานสองข้อแรก (และคุณรู้ว่าพวกเขาพูดอะไรเกี่ยวกับสมมติฐาน)

1. ข้อมูลกำลังถูกดึงออกจากฐานข้อมูลและส่งคืนเป็นแถวซึ่งอาจไม่เรียงตามลำดับ ตัวอย่างเช่น:

 ATLANTA, GA     IN-AK, HI  3  IN-CA  2  ...  IncidentOnly  4
 BOCA RATON, FL  IN-AK, HI  3  IN-CA  6  ...  IncidentOnly  5 
 ATLANTA, GA     IN-AK, HI  1  IN-CA  0  ...  IncidentOnly  2 
 ...
 AURORA, IL      IN-AK, HI  7  IN-CA  3  ...  IncidentOnly  4 

2. คุณต้องการให้ผลิตภัณฑ์ประกันภัยทั้งหมดสรุปสำหรับแต่ละสำนักงาน จากนั้นแสดงในรูปแบบรายงานที่สวยงามยิ่งขึ้น

If these assumptions are true (or close to true), you could create a HomeOffice class that had a property for each type of insurance, then simply loop through the rows of data in the raw report and add each HomeOffice object to a collection so you get a unique list of offices.

ตัวอย่างจากโปรเจ็กต์ที่มีเสียงคล้ายกันที่ฉันทำ:

Raw Data:
Mary    2   6
Sally   4   9
Mary    4   1
Sally   3   8
Joe     1   4
Bob     3   7
Mary    6   9
Sally   8   4
Bob     4   8
Joe     2   6
Joe     4   5

Formatted Data:
Mary       12      16
Sally      15      21
Bob         7      15
Joe         7      15

ในการดำเนินการนี้ ให้เพิ่มโมดูลคลาส (แทรก -> โมดูลคลาส) และเปลี่ยนชื่อเป็น HomeOffice ใส่รหัสนี้ลงในชั้นเรียน (ข้ามบิตบางส่วนไปจึงไม่นานนัก กรอกข้อมูลในส่วนที่ต้องการเพิ่มคุณสมบัติสำหรับผลิตภัณฑ์ประกันภัยแต่ละรายการ)

Option Explicit

Private pOffice As String
Private pINAKI As Double
Private pINCA As Double
'... class properties left out for brevity
Private pIncidentOnly As Double


''''''''''''''''''''''
' Office property
''''''''''''''''''''''
Public Property Get Office() As String
    Office = pOffice
End Property
Public Property Let Office(Value As String)
    pOffice = Value
End Property

''''''''''''''''''''''
' INAKI property
''''''''''''''''''''''
Public Property Get INAKI() As Double
    INAKI = pINAKI
End Property
Public Property Let INAKI(Value As Double)
    pINAKI = Value
End Property

''''''''''''''''''''''
' INCA property
''''''''''''''''''''''
Public Property Get INCA() As Double
    INCA = pINCA
End Property
Public Property Let INCA(Value As Double)
    pINCA = Value
End Property

''''''''''''''''''''''
' Add other propertied for the different product types
''''''''''''''''''''''
' Follow the same format as the other properties

''''''''''''''''''''''
' IncidentOnly property
''''''''''''''''''''''
Public Property Get IncidentOnly() As Double
    IncidentOnly = pIncidentOnly
End Property
Public Property Let IncidentOnly(Value As Double)
    pIncidentOnly = Value
End Property

ตอนนี้ใน CommandButton23_Click ย่อยของคุณให้เพิ่มโค้ดนี้ (ย่อให้สั้นลงอีกครั้งเพื่อความกระชับ แต่หวังว่าคุณจะได้ภาพ):

Sub test()
    Dim col As Collection
    Dim r As Integer
    Dim c As Integer
    Dim HO As New HomeOffice

    'Collections can only have one Item, Key pair. 
    'We'll use the office location as the key to get a 
    'unique list of offices
    Set col = New Collection

    'Read in the raw data
    With Sheet1
        For r = 1 To .UsedRange.Rows.Count
            'Check if the location has an existing HomeOffice object 
            If InCol(col, .Cells(r, 1)) Then
                'It does so get the existing object and total the values
                Set HO = col.Item(.Cells(r, 1))
                HO.Office = .Cells(r, 1)
                HO.INAKI = HO.INAKI + .Cells(r, 2)
                HO.INCA = HO.INCA + .Cells(r, 3)
                ' more properties
                HO.IncidentOnly = HO.IncidentOnly + .Cells(r, 10)
                'We have to remove the existing object and add it again
                'to reflect the updated totals
                col.Remove (.Cells(r, 1))
            Else
                'The location hasn't been added yet so create and add it
                HO.Office = .Cells(r, 1)
                HO.INAKI = .Cells(r, 2)
                HO.INCA = .Cells(r, 3)
                ' More properties
                HO.IncidentOnly = .Cells(r, 10)
            End If
            col.Add HO, .Cells(r, 1)
            'Important to clear our object or our totals are wrong! :)
            Set HO = Nothing
        Next r
    End With

    'Now we simply loop through our collection of offices and
    'print out the totals.
    r = 6 'The first office starts on row 6 in your picture
    With Sheet2
        For Each HO In col
            .Cells(r, "A").Value = HO.Office
            .Cells(r + 1, "C").Value = "IN - AK, HI"
            .Cells(r + 1, "F").Value = HO.INAKI
            .Cells(r + 2, "C").Value = "IN - CA"
            .Cells(r + 2, "F").Value = HO.INCA
            'Continuing on for all 10 types
            .Cells(r + 10, "C").Value = "Incident Only"
            .Cells(r + 10, "F").Value = HO.IncidentOnly
            Set HO = Nothing
            r = r + 13 'So the next office starts 13 rows later...Row 19 in your pic
        Next
    End With
End Sub

Function InCol(col As Collection, key As Variant) As Boolean
    'Returns TRUE if the object is in the collection or FALSE if it is not
    Dim obj As New HomeOffice

    On Error GoTo err
    InCol = True
    'If the key doesn't exist, it throws an error and set the function to false
    Set obj = col(key)
    Set obj = Nothing
    Exit Function

err:
        InCol = False
End Function

นี่เป็นแนวทางที่แตกต่างออกไปอย่างมาก และเกี่ยวข้องกับแนวคิดที่เข้มงวดกว่านี้ อย่างที่ฉันบอกไป มันอาจไม่ทำงานขึ้นอยู่กับวิธีการจัดรูปแบบข้อมูลดิบ แต่บางทีมันอาจทำให้คุณมีแนวทางที่แตกต่างในการแก้ไขปัญหาของคุณได้