Excel VBA - Copy Values from Row First and Paste into Column

Question

This is continuation of my question from here : How to looping rows and then columns in Excel

I have stumbled another roadblock as I pull all night over this issue: To recap:

I have a table as shown below (B1:L7) Where A1 is lookup values , row B is the header , row C to L is the data.

Column N is the visual representation of end result going to looked like. It is bolded and highlighted for clarity.

~~Note : Highly discouraged solutions of selecting the whole row and transpose paste due to there are conditional formatting at column N for further analysis.~~

Here is what I intend to do with the macro below:

Loop the row B using the lookup values in A1 for matching- DONE
Once macro found matching values to lookup values, (i.e :B6 shows matching values to A1) , the values of first 10 values (C to L) (i.e:row 6) are looped to show the values - DONE
~~All of 10 values are copied at columns N (starting N1 and reiterates downwards to N10) (i.e:C6 values are copied to N1 , D6 to N2 , etc...)~~
While iterating thru the rows , select the range and paste transpose the values selection in cell N1

    Sub Looping_Click()
    'Search columns
    Dim c As Range
    'Search rows
    Dim r As Range
    'Range to copy and paste values
    Dim i As Range
    
    For Each r In Range(Range("B1"), Range("B1").End(xlDown))
        If r.Value = Range("A1").Value Then
            MsgBox "Found values at " & r.Address
            
            For Each c In Range(r.Offset(0, 1), r.Offset(0, 10))
                MsgBox "Values is " & c.Value
                ''''''''''''''''''''''''''''''''''''''
                MsgBox "Values is " & c.Value
                r.Selection.Copy
                Next i
                ''''''''''''''''''''''''''''''''''''''
                Range("N1").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Next c
        End If
    Next r
    End Sub

The problem is when I running macro, there are no values being pasted at column N as well as RunTimeError 438 pops out I have highlighted the related / suspected troublesome macro parts with ''''

You do know you can paste values only while transposing, right? That won't overwrite conditional formatting. — BigBen
– BigBen, Commented Feb 4, 2021 at 1:26
Or you can use Application.Transpose to transpose the values. — BigBen
– BigBen, Commented Feb 4, 2021 at 1:30

Variatus · Accepted Answer · 2021-02-05 02:07:36Z

4

Please try this approach.

Sub Looping_Click()
    ' 167
    
    Dim Fnd         As Range        ' target to find
    Dim Arr         As Variant      ' values in found row
    Dim R           As Long         ' targeted row

    ' find the value of cell A1 in column B (=columns(2))
    Set Fnd = Columns(2).Find(Cells(1, "A").Value, , xlValues, xlWhole)
    If Fnd Is Nothing Then
        MsgBox "The requested value wans't found.", _
               vbInformation, "Unsuccessful search"
    Else
        ' define a range from the cell where the match was found,
        ' starting 1 cell to the right and then 10 cells wide, 1 row high
        ' read all found values from that range into an array
        Arr = Fnd.Offset(0, 1).Resize(1, 10).Value
        
        ' define a range from the cell N1, make it the same size as the array,
        ' then paste the array to the target range transposing the one column into one row.
        Cells(1, "N").Resize(UBound(Arr, 2), UBound(Arr)).Value = Application.Transpose(Arr)
    End If
End Sub

EDIT:

Referring to your comment, clarity is in the eye of the beholder but one argument would be that the fewer parts a machine has the less complicated it is and therefore the easier it will be to maintain. The above procedure has 3 parts.

Find the matching row.
Copy the values from that row
Paste the copied values to a destination.

edited Feb 5, 2021 at 2:07

answered Feb 4, 2021 at 3:55

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alan Koh W.T Over a year ago

Hi Variatus, thanks for your help but I prefer the code to have clearer style for sake of maintainability in the future. Still stuck on the part of where to copy the data / paste transpose to columns

Variatus Over a year ago

Hi Alan, it's impossible to read the same code twice with the same knowledge because one learns from what one reads. My code is extremely simple and therefore exceedingly easy to maintain. I have added more comments, however, to facilitate reading it.

Collectives™ on Stack Overflow

Excel VBA - Copy Values from Row First and Paste into Column

1 Answer 1

EDIT:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related