Snasui Excel Forum

Code: Select all

Public Sub Drawlayout()
    Dim i As Integer
    Dim no_c As Single 'number of customer
    Dim nb() As Integer 'ËÁÒÂàÅ¢à¤Ã×èͧ
    Dim mc() As String 'ª×èÍà¤Ã×èͧ¨Ñ¡Ã
    Dim rowstart() As Integer 'rowàÃÔèÁµé¹
    Dim columstart() As Integer 'columàÃÔèÁµé¹
    Dim rowend() As Integer 'row¨º
    Dim columend() As Integer 'colum¨º
    Dim sourceColor() As Long 'ÊÕ
    
    i = 3
        no_c = 0
        Do Until Worksheets("Data").Cells(i, 1).Value = ""
            
            i = i + 1
            no_c = no_c + 1
            
        Loop
    ReDim nb(1 To no_c)
    ReDim mc(1 To no_c)
    ReDim rowstart(1 To no_c)
    ReDim columstart(1 To no_c)
    ReDim rowend(1 To no_c)
    ReDim columend(1 To no_c)
    ReDim sourceColor(1 To no_c)
    
    i = 1
    Worksheets("layout").Cells.clear
    Worksheets("layout").Activate
    For i = 1 To no_c
        nb(i) = Worksheets("Data").Cells(i + 2, 1).Value 'ËÁÒÂàÅ¢à¤Ã×èͧ
        mc(i) = Worksheets("Data").Cells(i + 2, 2).Value 'ª×èÍà¤Ã×èͧ¨Ñ¡Ã
        rowstart(i) = Worksheets("Data").Cells(i + 2, 3).Value 'rowàÃÔèÁµé¹
        columstart(i) = Worksheets("Data").Cells(i + 2, 4).Value 'columàÃÔèÁµé¹
        rowend(i) = Worksheets("Data").Cells(i + 2, 5).Value 'row¨º
        columend(i) = Worksheets("Data").Cells(i + 2, 6).Value 'colum¨º
        sourceColor(i) = Worksheets("Data").Cells(i + 2, 7).Interior.Color 'ÊÕ
    Next i
    
    Dim a As Integer, b As Integer, z As Integer, y As Integer
    Dim start As String
    Dim C_end As String
    
    
    i = 1
    For i = 1 To no_c
    
    a = rowstart(i)
    b = columstart(i)
    y = rowend(i)
    z = columend(i)
    
    
    With Worksheets("Layout")
        .Range(.Cells(a, b), .Cells(y, z)).Value = i
        .Range(.Cells(a, b), .Cells(y, z)).Interior.Color = sourceColor(i)
    End With
    Next i
      
    Worksheets("Layout").Range("O1:O15").Borders(xlEdgeRight).LineStyle = xlContinuous
    Worksheets("Layout").Range("A15:O15").Borders(xlEdgeBottom).LineStyle = xlContinuous
End Sub