help please

[sufianmalik]

i'm stuck.

column C contains a record and column B contains Sub headngs only i want the sub headings to be copied and next to each record in column C

for example
B1 Sub title
B2 - B38 are empty

C2-C38 have records. I would like a macro to copy B1 and paste to B2-B38

Then

B39 Contains a new heading
B40-B45 are empty

C40-C45 contain records. So it has to copy B39 into B40-B45

There are several other headings that need copying in the same way with differing numbers of records

Is there a quick way of doing this?

 

[VoG]

Try

Sub fillem()
Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
With Range("B1:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[sufianmalik]

thanks for your reply that came up with a debug error on the specialcells row

i'm a basic vba user so don't know what to do to fix!

 

[VoG]

Column B must contain spaces, not blanks. Try selecting column B then running TrimAll http://dmcritchie.mvps.org/excel/join.htm

Then try my code again.

 

[sufianmalik]

i had to do something a little more basic and came up with the following:

i know that B8 is the first title but i don't know how far down it should go before it gets to the next title and would therefore have to copy this new value until blank. the only other issue is that some sections have more than one title...for example main section heading, then the cell below has a sub section then the cell below has a lower sub section, it would be this value that needs to be copied.

is this too long winded?!!

Sub must_copy_wbs()

Range("b8").Select
ActiveCell.Copy

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value <> ""

If ActiveCell.Offset(0, 1).Value <> "" Then ActiveCell.PasteSpecial

ActiveCell.Offset(1, 0).Select

Loop

End Sub

 

[sufianmalik]

i'm getting what i want but it seems i have to keep copying the loop code over and over...what am i doing wrong?

Sub must_copy_wbs()

Range("b8").Select
ActiveCell.Copy

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value <> ""

If ActiveCell.Offset(0, 1).Value <> "" Then ActiveCell.PasteSpecial

ActiveCell.Offset(1, 0).Select

Loop


ActiveCell.Copy

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value <> ""

If ActiveCell.Offset(0, 1).Value <> "" Then ActiveCell.PasteSpecial

ActiveCell.Offset(1, 0).Select

Loop



ActiveCell.Copy

ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value <> ""

If ActiveCell.Offset(0, 1).Value <> "" Then ActiveCell.PasteSpecial

ActiveCell.Offset(1, 0).Select

Loop


End Sub

 

[VoG]

What does this formula return?

=CODE(B9)

assuming that B9 appears blank.