CODEWITHSUNDEEP
excelvba
Flowfire
Flowfire

Reputation: 3

How to to convert a single column into multiple columns?

I'm trying to convert a single column into multiple columns. 

Set rng1 = Range("A3")
Set rng2 = Range("A16")
Set rng3 = Range("B3")
Set rng4 = Range("A16")

For Each R In Intersect(Range("A1:A5000"), ActiveSheet.UsedRange)

    If InStr(R, "Sunday") > 0 Then

        Range("C1").Value = 5

        If rng2.Row > rng1.Row Then
            Range("C1").Value = 6
            Range("F3").EntireColumn.Insert
            Range("F3").EntireColumn.Insert
            Range("C1").Value = 9
            Range(rng2, rng3.End(xlUp)).Cut
            Range("C1").Value = 10
            Range("F3").PasteSpecial
            Range("C1").Value = 7

It consistently fails at the PasteSpecial line.

Run-time Error 1004 Application-defined or object-defined error

I'm currently working off the assumption that it's trying to paste it above A1.

The reason behind going up from the bottom is so I can offset a line on the bottom of each new column.

EDIT: Tried removing PasteSpecial and just pasting it using the cut method, but it still fails.

EDIT: Tried the line 

Range("F1").Value2 = Range(rng2, rng3.End(xlUp)).Value2

As a replacement for cutting and pasting, still throws me the same error. Though the code continues on past it. It also doesn't paste anything.

EDIT: Uhh, so, it does copy entries over now, but not the values I want, and only a single one.

Upvotes: 0

Views: 112

Answers (1)

Marlond25
Marlond25

Reputation: 77

What if you bypass the paste method by directly assigning values from one cell to another:

Sheet2.Range("A2:B3").value2 = Sheet1.Range("A2:B3").value2

Is faster and cleaner, it can also be used with arrays and variables. So you can split the range within how many arrays you want, and then use the code above to take the values into desired columns.

Upvotes: 1

Related Questions