need vba code to copy set of data in row to column

**johnlara** · 04-11-2021, 06:39 AM

Dear experts

I have set of data in sheet rawdata which is in row down as set of data need to copy column in separate header value

please find the attachment

**jindon** · 04-11-2021, 07:26 AM

Sub test()
    Dim r As Range, n As Long, x
    For Each r In Columns(1).SpecialCells(2, 1)
        If r.Text Like "##:##*" Then
            n = n + 1: x = Split(r(-2))
            Cells(n + 1, "e").Resize(, 8) = Array(r(-2), r(-3), r(-1), r(0), r.Text, r(2), _
                                            IIf(r(3) = "", 0, r(3).Text), x(UBound(x)))
        End If
    Next
End Sub

**johnlara** · 04-11-2021, 10:10 PM

Thanks for perfect code which is works amazing one thing which is not copy data till end of row where it ends of data

please find real data in xls

**mohadin** · 04-11-2021, 07:43 AM

Hi

Sub test()
    Dim ar As Range
    Dim a As Variant
    Dim i As Long
    i = 1
    For Each ar In Sheets("rawdata").Columns("A:A").SpecialCells(2, 23).Offset(1).Areas
        a = Application.Transpose(ar)
        ReDim Preserve a(1 To UBound(a) + 1)
        a(7) = IIf(a(7) = "", 0, a(7))
        a(8) = Split(a(2), "$")(1)
        Sheets("EXPECTED Result").Cells(2 + i, 1).Resize(, 8) = Application.Index(a, 0, Array(2, 1, 3, 4, 5, 6, 7, 8))
        i = i + 1
    Next
    Sheets("EXPECTED Result").Columns("E").NumberFormat = "h:mm"
End Sub

**johnlara** · 04-11-2021, 09:56 PM

WorKs Amazing but small END WITH "error 9 subscript out of range"

**jindon** · 04-12-2021, 01:49 AM

Change

        If r.Text Like "##:##*" Then

to

        If (r.Text Like "##:##") + (r.Text Like "#:##") Then

**johnlara** · 04-12-2021, 06:22 AM

Thanks it works perfectly

need vba code to copy set of data in row to column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

need vba code to copy set of data in row to column

Re: need vba code to copy set of data in row to column

Re: need vba code to copy set of data in row to column

Re: need vba code to copy set of data in row to column

Re: need vba code to copy set of data in row to column

Re: need vba code to copy set of data in row to column

Re: need vba code to copy set of data in row to column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How does the VBA code copy the data in column A to the merged cell in column D?

[SOLVED] VB Code to copy paste data from column to another column based on content of cell

[SOLVED] VBA code: copy/paste data from 2 two columns into one column

VBA to copy down Vlookup VBA code in Column till last available data

[SOLVED] Find a value in a column and copy all data above that row code not working.

[SOLVED] VBA code to copy column data only if headers match

VBA Code to copy data from last filled column and paste to the next available column.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions