Excel script/formula to copy cell if above cell is null

March 14, 2012 at 19:45:17
Specs: Macintosh
Hello,

Can anyone help?

I need to run a script that will determine if a1 is blank, paste the information from a2 into the a1. If a1 is not null, it should do nothing. This needs to be run for every other cell. If a3 is null, paste information from a4. If a3 is not null, do nothing. And so on, and so on.

Any ideas?? Thanks in advance!

<config>Mac OS X / Firefox 10.0.2</config>


See More: Excel script/formula to copy cell if above cell is null

Report •

#1
March 15, 2012 at 07:22:45
This code should do what you ask for A1:A21.

You should try this code in a back up copy of your workbook since Macros cannot be easily undone.

Sub CopyIfNotBlank()
  For rw = 1 To 21 Step 2
   If Cells(rw, 1) = "" Then _
    Cells(rw, 1) = Cells(rw + 1, 1)
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 15, 2012 at 08:31:29
Thanks for your help. I was wrong when I gave you the information. It should be a1 and B1, A2, b2. The cells to be copied are alternating rows, not columns. SORRY!

Report •

#3
March 15, 2012 at 10:20:55
Your second post doesn't make sense.

"The cells to be copied are alternating rows, not columns"

The code I provided is based on every other row A2, A4, etc. as requested.

Now you say it should be B1 copied to A1, yet you says that the cells to be copied are alternating rows, which is what I provided. That's pretty confusing.

If you really want Column B copied to Column A if Column A is blank, just use an IF function in any other column and then use Copy/PasteSpecial...Values to place the results into Column A.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 15, 2012 at 10:36:04
Sorry!! If a1 is blank, it needs to have data from b1 pasted into it. If c1 is blank, it needs the data from d1. Example:

ZIP5 ZIP4 LATITUDE LONGITUDE CAICAT BBSERVICE PUBLICWIFI
60124 42.53465 -88.346439 3 Y
60124 5343 42.053465 -88.346439 3 Y U
67534 42.8734 -88.346439 3 Y
67534 3533 42.053465 -88.346439 3 Y U


In this case, I need every other row populated by what is below it. Not what is to the left or right.... Does that make sense? I tried the other macro with no luck... Thanks again so much for your help!!!


Report •

#5
March 15, 2012 at 11:06:23
OK, so now you changed it for the third time.

First post:

A2 to A1
A4 to A3

Second post:

B1 to A1
B2 to A2

Third Post:

B1 to A1
D1 To C1

Yet in the same third post you said:

"In this case, I need every other row populated by what is below it"

That's exactly what you asked for in your first post and doesn't match the cell references in your third post.

I respectfully give up.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question