Copy and paste depending on next field

[markdt]

Hi All,

I have a problem.

My scenario is:
If i have A in column A1 then i have B in column A5 and then i have C in column A10 for example, I would like to paste A down to where B starts, then B down to C etc...

Im not too good with Excel so any help would be very much appreciated.

Thanks

Mark

 

[Fenrirshowl]

I wouldn't even use VBA for this (unless you have to). In a free column (I will refer to column B) enter the following:

cell B1 formula: =A1
cell B2 formula: =if(A2<>"",A2,B1)

then copy the formula in B2 down as far as you need. If you need these results in column A simply copy the new column of functions and pastespecial (under the edit menu) and paste as values in column A.

Using VBA (if required):

Sub FillIn()
For x=2 to 100 ' change 100 to whatever row you need to go to
    If cells(x,1)="" then cells(x,1)=cells(x-1,1)
next x
End Sub

Note the ",1)" in the IF statement refers to the column number so A=1, B=2 etc.

Hope this helps.

Fen

 

[markdt]

Thank you very much, just what i was looking for.

Mark

 

[Numbers1]

Use the Macro recorder to get an idea of what the code would look like for starters. It is in Tools, Macros, Record. While the recorder is on, go through your series of cut, copy, paste procedures and then stop the recorder and look at the code.
You will have to do a little work to move to the next activecell but start here first.
Numbers