Change format of data file...in Excel 2007

Microsoft Office 2007 basic edition with...
August 28, 2010 at 14:25:41
Specs: Windows Vista
I have a data file in a single column that is 6000 dates. What I would like to do is parse out all 6000 dates into:
1) groups of 5
2) format is horizontal cells (B4,C4,D4,E4, and F4)

Basically, take the date in cell (A1) and place it into cell (B1)
next date in cell (A2) and placed into cell (C1)
next date in cell (A3) and placed into cell (D1)
next date in cell (A4) and placed into cell (E1)
next date in cell (A5) and placed into cell (F1)

and then repeat this same process fro the next group of 5, however the output would be in row 6 (Example: B6,C6,D6,E6,and F6) and so on for the balance of the file.

I created a macro for the first group, but I do not know how to expand the range to work the entire data column.

Can someone help?


See More: Change format of data file...in Excel 2007

Report •


#1
August 28, 2010 at 19:29:09
Why not post the macro that you wrote for the first group and we'll see if we can suggest a way to modify it to meet your needs?

Post your code between pre tags to keep the formatting of the code intact. Click the pre icon above the Reply box to insert the pre tags.

Make sure your code includes comments so that it's easier for us to figure out what you are trying to do.


Report •

#2
August 28, 2010 at 23:36:41
Sub Horizontal()
'
' Horizontal Macro
' Move 5 dates from column  to row format
'
' Keyboard Shortcut: Ctrl+s
'
' select cell
    Range("Y74").Select
' move target cell data to destination cell
    Range("Y74").Cut Destination:=Range("Z74")
' select cell
    Range("Y75").Select
' move target cell data to destination cell
    Range("Y75").Cut Destination:=Range("AA74")
' select cell
    Range("Y76").Select
' move target cell data to destination cell
    Range("Y76").Cut Destination:=Range("AB74")
' select cell
    Range("Y77").Select
' move target cell data to destination cell
    Range("Y77").Cut Destination:=Range("AC74")
' select cell
    Range("Y78").Select
' move target cell data to destination cell
    Range("Y78").Cut Destination:=Range("AD74")
    Range("AD74").Select
End Sub

Would you like to have a sample data set to work with?


Report •

#3
August 29, 2010 at 05:42:03
Hi,

To start with some suggestions.

In Visual Basic it is not usually necessary to select a cell (or any other object) before doing something with it.

Range("Y74").Cut Destination:=Range("Z74")
works fine even if Y74 was not selected

Rather than moving cells individually, work on a range of cells
Range("Y74:Y78").Cut Destination:=Range("Z74")
will move the 5 selected cells to Z74 and the 4 following cells

In your case because your destination range is horizontal not vertical, we have to use a two-part copy and Paste Special, which allows the use of transpose:
Range("Y74:Y78").Copy
Range("Z74").PasteSpecial Paste:=xlPasteAll, Transpose:=True
(You can't use Cut before Paste Special, so Copy has to be used. The source data could be cleared as a separate step)

Because you are doing a repetitive action, you need a loop
and as the cells are being handled in groups of 5, then you can either go through each source cell and increment a counter x=x+1 and then reset it whe you get to 5
If x=5 then
x=1
End If
or step through your source range in steps of 5
For n = 1 to 6000 Step 5

Next n

To make the code easier to modify, especially years after it was written, I try to setup most variables at the start of the program, even though I could have written the program without them.
For example:
'set first source row
lngFirst = 1
'set last source row
lngLast = 6000
'loop
for n= lngFirst to lngLast Step 5

... could have been written
'loop
For n = 1 to 6000

To help with writing the code, I prefix variables with an identifier as to their type such as strName for a variable that is of the string type, or lngLast for a variable of the Long number type. It makes it easier to avoid using the wrong variable type.

Always use Dim to setup your variables
Dim lngStart as Long
This helps with the Intellisense that provides prompts/options as you write the code, and sets variables to the required type - if they are not explicitly DIM'd they default to a type known as Variant.

Anyway here is my offering on how to do your cut and paste every 5 rows.
I setup my example with a source in column A and the destination one row to the right (column B)

You will see that I selected groups of 5 cells using the Resize property and the starting point of the destination uses the Offset function.
.Resize(RowSize, ColumnSize)
.Offset(RowOffset, ColumnOffset)

You will need to specify the location of the start of your source data.
The end of the data is located in the same column and uses the .End property with the xlUp option, as the starting point for the search for the end is the last row in the specified column.
As versions of Excel have different numbers of rows, the last row is obtained by Application.Rows.Count

Option Explicit

Sub MoveInFives()
Dim rngStart As Range
Dim rngEnd As Range
Dim lngStart As Long
Dim lngEnd As Long
Dim n As Long

'set ranges  using the active worksheet
With ActiveSheet
    'set start of cells to move
    Set rngStart = .Range("A1")
    'find end of used cells in same column
    Set rngEnd = .Cells(Application.Rows.Count, rngStart.Column).End(xlUp)
End With

'setup start and end rows
lngStart = rngStart.Row
lngEnd = rngEnd.Row

'adjust end of range if necessary to a multiple of 5
lngEnd = 5 * Int((lngEnd - lngStart + 1) / 5)

'loop through all the cells with data, in steps of 5
For n = lngStart To lngEnd Step 5
    'copy 5 cells in column A
    'row offset is one less than row number
    'i.e., first offset is zero not 1
    rngStart.Offset(n - 1, 0).Resize(5, 1).Copy
    'paste/transpose the 5 cells to the next column
    'in the same row as start of copied cells
    rngStart.Offset(n - 1, 1).PasteSpecial _
                    Paste:=xlPasteAll, _
                    Transpose:=True
    'clear the source data
    rngStart.Offset(n - 1, 0).Resize(5, 1).ClearContents
Next n

End Sub

An alternative, looping through each cell, uses the For Each ... In construct, which allows you to go through each item in a collection, in this case a collection (range) of cells.
A variable is required to keep track of the groups of 5 cells.

Sub MoveInFivesB()
Dim rngCell As Range
Dim rngStart As Range
Dim rngEnd As Range
Dim rngSearch As Range
Dim intFives As Integer

'set ranges  using the active worksheet
With ActiveSheet
    'set start of cells to move
    Set rngStart = .Range("A1")
    'find end of used cells in same column
    Set rngEnd = .Cells(Application.Rows.Count, rngStart.Column).End(xlUp)
    'setup search range
    Set rngSearch = Range(rngStart.Address, rngEnd.Address)
End With

'set five's counter
intFives = 1

'loop through all cells in source range
For Each rngCell In rngSearch
    If intFives = 1 Then
        'start of group, so copy & Paste/transpose
        rngCell.Resize(5, 1).Copy
        rngCell.Offset(0, 1).PasteSpecial _
                            Paste:=xlPasteAll, _
                            Transpose:=True
        ElseIf intFives = 5 Then
        'fifth row so delete data in last 5 rows & reset counter
        rngCell.Offset(-4, 0).Resize(5, 1).ClearContents
        intFives = 0
    End If
    'increment fives counter
    intFives = intFives + 1
Next rngCell
End Sub

Regards


Report •

Related Solutions

#4
August 29, 2010 at 05:57:07
Some major advantages of VBA are:

1 - Rarely do you have to Select an object to perform an operation on it.

For example, this:

' select cell
    Range("Y74").Select
' move target cell data to destination cell
    Range("Y74").Cut Destination:=Range("Z74")

can be condensed to this:

' Cut and paste cell
    Range("Y74").Cut Destination:=Range("Z74")

2 - Variables can be used for Row (and Column) references.

For example, this:

Range("Y74").Cut Destination:=Range("Z74")

Can be written as this:

myRw = 74
Range("Y" & myRw).Cut Destination:=Range("Z" & myRw)

3 - Variables can be used in For-Next loop.

For example, this:

'Cut and past Y74
 myRw = 74
  Range("Y" & myRw).Cut Destination:=Range("Z" & myRw)
'Cut and past Y74
 myRw = 75
  Range("Y" & myRw).Cut Destination:=Range("Z" & myRw)

Can be written as:

'Cut and Paste Y74:Y75
 For myRw = 74 to 75
  Range("Y" & myRw).Cut Destination:=Range("Z" & myRw)
 Next

In addition, Ranges can be designated in various manners. For example,

Range("Y74") can be writen as Cells(74, "Y") or Cells(74, 25)

Finally, variables used for can be row and columns numbers can be incremented based on given conditions.

Putting all of that together, you might try something like this.

Tip: Use F8 to single step through this to watch how it flows through the code, especially after it has moved 6 pieces of data.

Option Explicit
Sub TransposeData()
Dim myDst_Rw, myDst_Col, mySrc_Rw, rw_Counter As Integer
'initial Destination Row and Column variables
  myDst_Rw = 74
  myDst_Col = 26
''Loop to Cut and Paste data
   For mySrc_Rw = 74 To 6074
'Cut and Paste a cell
     Cells(mySrc_Rw, 25).Cut Destination:=Cells(myDst_Rw, myDst_Col)
'Increment the Destination Column
      myDst_Col = myDst_Col + 1
'Increment the rw_Counter
      rw_Counter = rw_Counter + 1
'Have we moved 6 piecs of data?
        If rw_Counter = 6 Then
  'If yes...
     'reset rw_Counter
           rw_Counter = 0
     'Increment Destination Row
          myDst_Rw = myDst_Rw + 1
     'Reset Destination Column
          myDst_Col = 26
        End If
   Next
End Sub

Instead of Cut/Paste, you could also make this a bit more efficient by looping through a Copy...PasteSpecial...Transpose using many of the same techniques as I used.

Why not try and see if you can get that to work? I think you'd learn a lot.


Report •


Ask Question