Transfer to array

[mahmed1]

Hi All

Hi can i transfer this code into an array and then output as its takes 3-4 minutes to update

Sub Transpose_Attendance()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lrow As Long
Dim lcol As Long
Dim i As Long
Dim c As Long
Dim dDate As Variant
Dim lrow2 As Long

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Worksheets("Attendance")
Set ws2 = ThisWorkbook.Worksheets("tbl_Attendance")

lrow = 58
lcol = 13
lrow2 = 2

For i = 5 To lrow
    dDate = ws.Range("B" & i).Value
    For c = 3 To lcol
        ws2.Range("A" & lrow2).Value = dDate
        ws2.Range("B" & lrow2).Value = ws.Cells(4, c).Value
        ws2.Range("C" & lrow2).Value = ws.Cells(i, c).Value
        lrow2 = lrow2 + 1
    Next c
Next i

Application.ScreenUpdating = True

End Sub

Thank You

 

[mumps]

Rather than trying to decipher your code, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[kvsrinivasamurthy]

In tbl_Attendance sheet.
In A2

=INDEX(Attendance!$B$1:$B$306,5+INT((ROWS($A$2:$A2)-1)/11))

In B2

=Attendance!$C$4

In C2

=INDEX(Attendance!$C$1:$C$306,5+INT((ROWS($A$2:$A2)-1)/11))

All formulas are copied down up to Row 595.

 

[mahmed1]

Thank you