PDA

View Full Version : Cell Change Help



mtang21
09-12-2008, 06:06 AM
Hi everyone,

I have been racking my brain on how to make this work exactly. I need a macro or a formula atleast to make my data flow into a report.


Currently it looks like this:

Fund 1 09/12/2008 Dodge&Cox
PIMCO
American FUNDS
Janus
Vanguard

Fund 2 09/12/2008 Calamos
Goldman Sachs
State Street



What i need is this report to look like this:

Fund 1 09/12/2008 Dodge&Cox
Fund 1 PIMCO
Fund 1 American FUNDS
Fund 1 Janus
Fund 1 Vanguard

Fund 2 09/12/2008 Calamos
Fund 2 Goldman Sachs
Fund 2 State Street



I need to find a way to copy the top cell of each section to filter down to all its components but when the cell changes, i need it to stop copying that section and start copying the next section. I don't want to copy and paste the entire time. Any help would be tremendous! Thanks all!

Kenneth Hobs
09-12-2008, 06:55 AM
Is each line in one column?

mtang21
09-12-2008, 08:19 AM
fund 1 is 1 column...then date is another...then mutual fund is the last

Kenneth Hobs
09-12-2008, 12:30 PM
Sub SetFunds()
Dim fundStr As String
Dim firstCell As Range, nextCell As Range, curCell As Range, fCell As Range
Dim rNum As Long

rNum = -1
Set firstCell = Range("A1")
Set curCell = firstCell.Offset(0, 0)
Set nextCell = curCell.Offset(1, 0)
Do Until nextCell.Value = "" And curCell.Value = ""
If curCell.Address = firstCell.Address Then
Set fCell = curCell
Else: If curCell.Offset(-1, 0) = "" Then Set fCell = curCell
End If
If curCell.Address <> fCell.Address And curCell.Value <> "" Then
curCell.Offset(0, 1).Value = curCell.Value
curCell.Value = fCell.Value
End If
rNum = rNum + 1
Set curCell = firstCell.Offset(rNum, 0)
Set nextCell = curCell.Offset(1, 0)
Loop
End Sub

mdmackillop
09-14-2008, 03:08 AM
Sub SetFunds2()
Dim rng As Range, cel As Range
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each cel In rng
If Left(cel, 4) <> "Fund" And cel <> "" Then
cel.Insert Shift:=xlToRight
cel.Offset(, -1).FillDown
End If
Next
End Sub