Solved: Conditionally moving cell contents

[tehp]

I'm trying to move certain cell contents to different cells. In my attached example, A2 would move to I1, B2 to J1, A3 to K1, etc. This would need to be done for each company in the worksheet.

I know it's good practice to show you some code that I've tried, but I've written and deleted so much that I'm not sure which lines are on the right track.

I'm really new to this, but intend to learn and use VBA more frequently, so hopefully I can contribute to the forum more after I'm more knowledgable.

Can anyone help me out here? (I've felt like for two days.)

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim StartRow As Long
Dim i As Long
Dim data As Variant

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 1 To Lastrow + 1

If .Cells(i, "A").Value2 = "" Then

data = Application.Transpose(.Cells(StartRow, "A").Resize(i - StartRow))
.Cells(StartRow, "I").Resize(, i - StartRow) = data
StartRow = i + 1
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[tehp]

This is near perfect. I need the corresponding fee types in column B to be transposed as well. I tried adding that to your code, but did more harm than good. Would you be able to help me out with the rest?

Major, major thanks for you help. I wasn't aware of the transpose method and couldn't quite get copying and pasting to work.

(What a great community - you've inspired me to make a donation to the forum!)

[Bob Phillips]

Is this how you want it?

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim StartRow As Long
Dim i As Long
Dim data As Variant

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 1 To Lastrow + 1

If .Cells(i, "A").Value2 = "" Then

data = Application.Transpose(.Cells(StartRow, "A").Resize(i - StartRow, 2))
.Cells(StartRow, "I").Resize(2, i - StartRow) = data
StartRow = i + 1
End If
Next i
End With

Application.ScreenUpdating = True
End Sub[/vba]

[tehp]

Almost! All information for each company should be in a single row. So, A2 goes to J1, and B2 goes to K1. The fee type would be in the cell just right of the price.

I can't quite figure out how to edit the code you gave me to do this. I really need to practice my looping skills!

Thanks so much for all of your help. I'll be forever indebted if you can help me get this the way I want it.

[Bob Phillips]

Okay, we will have to do within the loop

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim StartRow As Long
Dim i As Long, j As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 1 To Lastrow + 1

If .Cells(i, "A").Value2 = "" Then

StartRow = i + 1
Else
.Cells(i, "A").Resize(, 2).Copy .Cells(StartRow, 9 + (i - StartRow) * 2)
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[tehp]

That's perfect! Thanks so much!