copy cell value down when cell next to it has a value

[frankandsteph]

Hello All, I think this is not as easy as I thought. What I have is a set of values in column A, lets say from A1:A10. Then I have one value in B1 and I want to copy that B1 value down as long as there is a value in A. Column A could be different lengths as in A1:A10 or A1:A1000, it is always different. The only constant is that column A will always be more than 1 value and column B will always start with 1 value and need to have that value copied down to match the length of column A.

Does this make sense?

-Frank

 

[Joe4]

Try VBA Code:

Sub MyCopy()
    Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row) = Range("B1")
End Sub

Note if you are looking for a non-VBA solution, auto-fill should work (just double-click in the lower right-corner of cell B1).

 

[frankandsteph]

This almost works, my description was off a bit, what if it all has to start in row 2, not row 1

 

[Joe4]

Then just increase all numbers by 1, i.e.

Sub MyCopy()
    Range("B[COLOR=#ff0000][B]3[/B][/COLOR]:B" & Cells(Rows.Count, "A").End(xlUp).Row) = Range("B[COLOR=#ff0000][B]2[/B][/COLOR]")
End Sub

 

[frankandsteph]

Actually I got it working, thank you. One last question, what if the data in Y2 is actually a formula. Now it pastes as a value, but want it to stay as a formula! That is the only column with a formula.

Thanks Again,
Frank

Sub MyCopy()
Range("F2:F" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("F2")
Range("G2:G" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("G2")
Range("H2:H" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("H2")
Range("I2:I" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("I2")
Range("J2:J" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("J2")
Range("K2:K" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("K2")
Range("L2:L" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("L2")
Range("o2" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("o2")
Range("P2:P" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("P2")
Range("Q2:Q" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("Q2")
Range("R2:R" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("R2")
Range("S2:S" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("S2")
Range("T2:T" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("T2")
Range("X2:X" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("X2")
'Range("Y2:Y" & Cells(Rows.Count, "E").End(xlUp).Row) = Range("Y2")
End Sub

 

[Joe4]

What exactly is the formula?
In the formula references other cells, should the cell row references increase as the formula gets copied down the column?

 

[frankandsteph]

Great question, the answer is yes D2 and E2 (as shown below) should increase as it moves down.

=RIGHT(BDP(D2&" "&E2,"PARSEKYABLE_DES_SOURCE"),4)

 

[Joe4]

Seeing the extent to what you are trying to do, I think this will be a little simpler:

Sub MyCopy()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
    Range("F2:L2").Copy Range("F3:F" & lastRow)
    Range("O2:T2").Copy Range("O3:O" & lastRow)
    Range("X2:Y2").Copy Range("X3:X" & lastRow)
End Sub

 

[frankandsteph]

That is perfect, thank you very much!

Frank

 

[Joe4]

You are welcome.