Cut And Paste Then Move TO The Cell Below.

[Gevaux19]

Hi,

I have a huge data file which goes across in different coloumns ie. Name - Address - Shares - Number - (Repeats across the screen)

I want to create a macro which can convert 4 coloumns at a time so it would look like:

Name -Address - Shares - Number
Name -Address - Shares - Number
(Onwards).

Please Help!

Thanks

Matt

[Siddharth Rout]

Welcome to the forums

Please confirm the Office version that you are using and the type of Data file that you are using...

[Gevaux19]

Thanks

I'm Using Excel 2007 and the data file is a .csv,

cheers

Matt

[Siddharth Rout]

Ok That shouldn't be a problem.

Search the forums on how to loop through excel cells. Also do you also know how to record a macro in 2007?

[Gevaux19]

Yea i do no how to record a macro but i'm not good at all and writing VB code.

Cheers

[Siddharth Rout]

Yea i do no how to record a macro but i'm not good at all and writing VB code.

Thats ok

Now did you search the forums on how to loop through excel cells.

[Gevaux19]

Yes, Nothing came up. ??

[RobDog888]

Convert columns as in Merging the columns so the four become a single column?

[Gevaux19]

No as One Go below another.

[Siddharth Rout]

@Rob; no Rob, it won't work that way. I suspect the data is in one row and the OP wants to arrange it in sets of 4 one below the other....

They are plenty of threads which will tell you how to loop thru excel cells. Can you upload a sample of your workbook?

[Gevaux19]

Yea i can upload, where do i upload it 2?

[Siddharth Rout]

Yea i can upload, where do i upload it 2?

http://www.vbforums.com/faq.php?faq=...b3_attachments

[Gevaux19]

Attahced is the sample of my workbook.

[Siddharth Rout]

You have to zip your excel file and then upload it...

[Ecniv]

Best way might be to open a connection to the file, read in characters and look for the tab character (9). (column) Count these and when count > 4 (or 5?) then move down a line and reset the column back to 1.

You can do this in vba coding :
- open
- close #
- left / right / mid <--- might be useful
- input <--- think this is the bit for getting a character

The help files in excel should cover these functions.


Alternatively you can use an API call to use the file as a stream and do the same, may be slightly quicker, but is harder to code.

Just options....

Is there any line/record delimiter?

[Gevaux19]

Ecniv,

I havent got a clue about Vb coding. lol.

Koolsid,

Just open it in excel with tab delimiter.

Regards

[Siddharth Rout]

Just a sample

Amend it as per your requirements...

Sub Sample()
    Dim i As Long
    
    '~~> Say data is in 1st Row starting from A1
    i = 1
        
    For i = 1 To 4
        '~~> So next range will start from E column
        Range("E" & i).Select
        '~~> Select till end
        Range(Selection, Selection.End(xlToRight)).Select
        '~~> Cut selection
        Selection.Cut
        '~~> Paste it one row below in column A
        Range("A" & i + 1).Select
        ActiveSheet.Paste
    Next
End Sub

[Ecniv]

Problem is that you are opening a huge datafile, and excel doesn't have a huge amount of columns... 4 per record, approx 255 columns, means not many records, or that the space below may contain data...

Hence the open and read a file.

If I get some time I'll write a simple import loop and comment it.. you can then tweak as applicable...

[Ecniv]

Option Explicit

Public Sub ImportDataFile()
    
    Dim sht As Worksheet
    Dim strChar As String
    Dim strColumnData As String
    Dim lngColumn As Long, lngRow As Long
    
    
    Set sht = ActiveSheet
    
'---- close the channel - incase its still open
    Close #1
    
    Open "C:\DataFile.csv" For Input As #1
    
    lngColumn = 1
    lngRow = 1
    strChar = String(1, " ")
    
'---- keep looping until the file runs out
    Do Until EOF(1)
        strChar = Input(1, #1)
        
'---- if its the separator
        If Asc(strChar) = 9 Then
            
'---- output the data and
'---- move the column
            sht.Cells(lngRow, lngColumn) = strColumnData
            lngColumn = lngColumn + 1
            strColumnData = ""
            
'---- if the column is > 4 then reset the column and add one to the row
            If lngColumn > 4 Then
                lngColumn = 1
                lngRow = lngRow + 1
            End If
        Else
        
'---- add the char to the data
'---- * you could add extra checks here to remove speechmarks etc
            strColumnData = strColumnData & strChar
        
        End If
    
    Loop
    sht.Cells(lngRow, lngColumn) = strColumnData
    '---- dump the last of the data in the cell
    
'---- close the channel
    Close #1
    
    
    Set sht = Nothing
End Sub

Drop that in a module - then run it and see if it works... (In excel!)
If it does, see if you understand how I did it.

might not be fast on huge files tho...