Re:vba 배열선언

[곽완근]

안녕하세요? 원하시는 형태가 맞는지 모르겠습니다.

Sub 편입통보()
    Dim n As New Collection
    Dim varT() As Variant
    Dim i As Long
    Dim lngR As Long
    Dim j As Long
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim c   As Range
   
    Set sht1 = Worksheets("편입")
    Set sht2 = Worksheets("편입통보")
   
    sht2.Cells.Clear
   
    lngR = Range("a" & intE).End(xlUp).Row
   
    Set n = Nothing
    For i = intS To lngR
        On Error Resume Next
        n.Add Range("q" & i).Value, CStr(Range("q" & i).Value)
       
        If Err.Number = 0 Then
            With Columns("Q:Q")
                Set c = .Find(Range("q" & i).Value, LookIn:=xlValues)
                If Not c Is Nothing Then
                    firstAddress = c.Address
                    Do
                        X = Application.CountIf(Columns("Q:Q"), c.Value)
                        ReDim Preserve varT(7, j)
                            varT(0, j) = Cells(c.Row, "I").Value '소유자
                            varT(1, j) = Cells(c.Row, "L").Value '주소
                            varT(2, j) = varT(0, j) & ", " & Cells(c.Row, "D").Value & " " & Cells(c.Row, "E").Value '편입소재지
                            varT(3, j) = varT(0, j) & ", " & Cells(c.Row, "G").Value '지적
                            varT(4, j) = varT(0, j) & ", " & Cells(c.Row, "H").Value '편입면적
                            varT(5, j) = X '필지수
                            varT(6, j) = Application.SumIf(Columns("Q:Q"), c.Value, Columns("G:G")) '총지적면적
                            varT(7, j) = Application.SumIf(Columns("Q:Q"), c.Value, Columns("H:H")) '총필지면적
                            j = j + 1
                        Set c = .FindNext(c)
                    Loop While Not c Is Nothing And c.Address <> firstAddress
                    Set c = Nothing
                End If
            End With
        Else
            Err.Clear
        End If
       
    Next i
       
   
    With sht2
        .Range("a1:h1") = Array("소유자", "소유자주소", "편입소재지", "지적", "편입면적", "필지수", "총지적면적", _
                                "총편입면적")
        .Range("a2").Resize(j, 8).Value = Application.Transpose(varT)
      
    End With
End Sub

[박현주.]

감사합니다^^

[박현주.]

한가지만 더 여쭤보겠습니다. 순환하면서 편입소재지, 지적, 편입면적 값 앞에 ", "가 붙는데 이것은 어떻게 하면 될까요? 배열 첫번째값이 없어서 그런 것같은데 어떻게 해야하나요?