Excel macro help needed

[Psychonaut 0]

Here's what I have.
I've got a huge excel file that I need to use macro's to move things around for easier processing.

Three columns, A B and C.
A and B are data values, C is a sound file used, eg wav1.wav.

So it will look something like:

29.4   22.4   wav1.wav

29.4   34.1   wav1.wav

74.2   58.2   wav1.wav

22.5   72.6   wav1.wav

45.2   89.5   wav2.wav

23.5   11.3   wav2.wav

And so on. The number of data entries per wav files is not consistent as seen above. What I need to do is every time that C column changes, is to move things after that to the right, so wav2.wav would be moved over to columns D E and F. And then wav3.wav would be G H and I.

I have a macro that finds where they change and adds a space, so it shouldn't be much of a change to then select the text and move it.

Sub InsertRowAtChangeInValue()

Dim lRow As Long

For lRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row To 2 Step -1

If Cells(lRow, "C") <> Cells(lRow - 1, "C") Then Rows(lRow).EntireRow.Insert

Next lRow

End Sub

Stay high pull low

[shropshire 0]

Here ... play with this....

Sub InsetCellandStuff()

Dim lRow As Long
Dim iCol As Long
Dim SomeNumber As Long

iCol = 3
SomeNumber = 100
For lRow = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1
If Cells(lRow, iCol) Cells(lRow - 1, iCol) Then
For x = 0 To SomeNumber
Cells(lRow + x, 1).Select
Selection.Insert Shift:=xlToRight
Next x
End If
Next lRow
End Sub
It doesn't do it all - gotta leave you something to do

(.)Y(.)
Chivalry is not dead; it only sleeps for want of work to do. - Jerome K Jerome

[DiverMike 5]

Insert a command button on your spreadsheet. Go into development mode and copy and paste the following:

Private Sub CommandButton1_Click()

Dim lngRow As Long

Dim j As Integer

Dim lngLastRow As Long


lngLastRow = Range("C65536").End(xlUp).Row ' assumes 'C' cell in last row is non empty


For lngRow = lngLastRow To 2 Step -1

If Cells(lngRow, 3) <> Cells(lngRow - 1, 3) Then

For j = lngRow To lngLastRow

Range("A" &  j  & ":C" & j ).Select

Selection.Insert Shift:=-4161, CopyOrigin:=0

Next j

End If

Next lngRow


End Sub

Click on the command1 button and celebrate.

Assumptions:
1. The last row on your spreadsheet has a value in it is column 'C'
2. You are only going to run this once. After you run it, nothing will be in column 'C' in the rows you shifted.

For the same reason I jump off a perfectly good diving board.

[Psychonaut 0]

Quote

Here ... play with this....

Sub InsetCellandStuff()

Dim lRow As Long
Dim iCol As Long
Dim SomeNumber As Long

iCol = 3
SomeNumber = 100
For lRow = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1
If Cells(lRow, iCol) Cells(lRow - 1, iCol) Then
For x = 0 To SomeNumber
Cells(lRow + x, 1).Select
Selection.Insert Shift:=xlToRight
Next x
End If
Next lRow
End Sub
It doesn't do it all - gotta leave you something to do

This somewhat works, but it only moves each grouping over by 2, so I added a second 'Selection.Insert Shift:=xlToRight' which leaves a gap between the groups but that can be dealt with later.

So now all the groupings stair step down to the right.
Now it's just a matter of shifting all of the groupings UP to the top of the page.

Any ideas?

http://img802.imageshack.us/img802/4333/capturer.jpg

Stay high pull low

[shropshire 0]

Here you go .....

Sub InsetCellandStuff()

Dim lRow As Long
Dim iCol As Long
Dim SomeNumber As Long
'find las row in range.
SomeNumber = Range("A65536").End(xlUp).Row
iCol = 3

For lRow = 1 To SomeNumber
If Cells(lRow, iCol) <> Cells(lRow + 1, iCol) Then
For x = 1 To SomeNumber
Cells(lRow + x, 1).Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Next x
MsgBox (lRow & ":" & iCol + 1 & ":" & iCol + 3)

Range(Cells(lRow, iCol + 1), Cells(1, iCol + 3)).Select
Selection.Delete Shift:=xlUp
iCol = iCol + 3
End If
Next lRow
End Sub

(.)Y(.)
Chivalry is not dead; it only sleeps for want of work to do. - Jerome K Jerome

[Psychonaut 0]

More progress, however it's having difficulties comparing them correctly..

Stay high pull low

[DiverMike 5]

I think you are going to have to step through your for/next backward in order to always check the 'C' cell. Otherwise, you move it over and there is nothing there.

Compressing the columns up would be another for/next finding the first non null or blank cell in each column, selecting the range - 1 and deleting up.

For the same reason I jump off a perfectly good diving board.

[linebckr83 3]

Just a thought, I would start at cell C1 and assign a variable to the wav file name. Then I would move down the C column, checking to see if the current file name matches the last one (checking for changes). Once it changes, I would copy the data from that row and below, offset 3 columns to the right and to row 1, paste the data. Then loop here so it does all that again but on column F instead. Until you get to the bottom of the list. I could write something for you but it would take me half an hour or so.

"Are you coming to the party?
Oh I'm coming, but I won't be there!"
Flying Hellfish #828
Dudist #52

[linebckr83 3]

This seemed to work for me.

Sub macro()

Dim wav_name As String


Range("A1").Select


Do While ActiveCell.Value <> ""

ActiveCell.Offset(0, 2).Select

wav_name = ActiveCell.Value

ActiveCell.Offset(1, 0).Select


Do

If ActiveCell.Value = wav_name Then

ActiveCell.Offset(1, 0).Select

Else

Exit Do

End If

Loop


Range(ActiveCell.Offset(0, -2), ActiveCell.End(xlDown)).Select

Selection.Cut

ActiveCell.End(xlUp).Offset(0, 3).Select

ActiveSheet.Paste


Loop


Range("A1").Select

End Sub

"Are you coming to the party?
Oh I'm coming, but I won't be there!"
Flying Hellfish #828
Dudist #52