Conditional copy paste using formula vs. VBA/Macro

Question

I'm importing data from another database.  Because of repeating data in columns to the right, the field that identifies that group of data is shown only once, on the first line of that series of data.   That group may have 25 records, but the identifier is only printed on the first line of that group.  When a new group data starts, a new identifier is shown in the identifying field, but again only on the first row of that group.

I want to have the identifier value repeat for each row of data that is applicable to it.  If this was just a few records, I would happily just copy the rows individually to the empty rows below it for each grouping of data.  But we're talking about hundreds of records, and I'd rather find a more elegant solution.

I'm trying to use a formula to automate a process, but with no success so far.  I think what I need is statement like this (psuedo-code):

if(cell is blank or equal to the clipboard, copy and paste the value that's in the clipboard, copy and paste the value of the current cell);

Let me make a sketch, perhaps it will help:

This is what I have:

           A                B             C

1      Red             five

2                          one

3                          nine      

4      Green         three

5                          three

6                          twenty

7      Yellow         four

8                          six

9                          twelve

This is what I want:

           A                B             C

1      Red             five

2      Red             one

3      Red             nine      

4      Green         three

5      Green         three

6      Green         twenty

7      Yellow         four

8      Yellow         six

9      Yellow         twelve

Thanks for trying :).  Sorry if this is confusing.  It's confusing to me, too.

Answer 1

You could also use a simple formula to "fill-it-in-from-above" in an adjacent col

Going by your sample data as posted in A1:B1 down

Put in C1: =A1

Put in C2: =IF(A2="",C1,A2)

Copy C2 down to the last row of data in col B. That's it.

Then just use col C for all your downstreams

Answer 2

or this code..

Sub AddInCells2()

Dim r As Long

r = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To r  Step 3

Cells(i, 1).Resize(3).Value = Cells(i, 1).Value

Next i

End Sub

Answer 3

Hi,

maybe a code

Sub AddInCells()

Dim r As Long

r = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To r + 1 Step 3

Cells(i, 1).Value = Cells(i - 1, 1).Value

Next i

For i = 3 To r + 2 Step 3

Cells(i, 1).Value = Cells(i - 1, 1).Value

Next i

End Sub

1) press ALT+F11 to open the Visual Basic Editor

2) select Insert > Module

3) Copy the  code and paste it on the right

4) close the VBE

To run the macro...

1) press ALT+F8 to show the Macros window

2) select the macro "AddInCells"

3) press Run.

Answer 4

Hi, this is a solution given by Debra

Start by selecting the empty cells:<br><ol><br><li>Select the cells in the column, starting in the row below the column heading.<br></li><li>Choose Edit	Go To </li><li>Click the Special button </li><li>Select Blanks, click OK </li></ol>
Enter the formula to copy the value:<br><ol><br><li>Type an equal sign </li><li>Press the up arrow on the keyboard -- this will enter a reference to the cell above -- cell A2 in this example<br></li><li>Hold the Ctrl key and press Enter -- this enters the formula in all selected cells<br></li></ol>
Change the formulas to values:<br><br>In order to sort or filter the data, the formulas must be changed to values.<br><br><ol><br><li>Select the entire column </li><li>Choose Edit	Copy </li><li>With the column still selected, choose Edit	Paste Special </li><li>Select Values, click OK </li></ol><br><br>Note: Do this carefully if there are other cells in the range which contain formulas

Answer 5

Thanks to everyone for your responses.  It looks like I need to brush up on my code a little.  And I want to try the formula approach also. I will let you know how it turns out!