By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,166 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,166 IT Pros & Developers. It's quick & easy.

How can I copy data from a cell to other cells on same line, testing that they are em

P: 1
Eg Copy A1 to B1 but if B1 is occupied try C1, etc until an empty cell on line 1 is found.
Nov 8 '12 #1
Share this Question
Share on Google+
11 Replies


zmbd
Expert Mod 5K+
P: 5,397
This is for MS-Excel yes?
Several ways to do this...
My way is to set the home cell and store the value
then use the offset from there to find an empty cell

Please post what you have tried so far remembering to format your code using the <CODE/> button.
Nov 8 '12 #2

NeoPa
Expert Mod 15k+
P: 31,494
  1. Move the selection cursor to cell A1.
  2. Is the value empty?
  3. Yes. All done. Write value here.
  4. No. Using ActiveCell.Offset(0, 1) check cell B1. Is the value empty?
  5. Yes. All done. Write value here.
  6. No. All done. Write the value in ActiveCell.End(xlToRight).Offset(0,1).
Nov 8 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
Why not just line 6?
Nov 8 '12 #4

NeoPa
Expert Mod 15k+
P: 31,494
Because it wouldn't work for either of the other two situations. That seems rather obvious. Is that really the question you meant to ask?
Nov 9 '12 #5

Rabbit
Expert Mod 10K+
P: 12,366
It seems to me the OP only has one scenario. Copy one cell to the first blank cell to the right of said cell.
Nov 9 '12 #6

NeoPa
Expert Mod 15k+
P: 31,494
:Confused:
... and that would not be achieved by using line #6 alone - but would be by following the steps as outlined.

This seems very straightforward to me. Am I missing something?
Nov 9 '12 #7

zmbd
Expert Mod 5K+
P: 5,397
That is what I gathered from OP's post too... kinda reminds me of a homework assignment which is why I asked for OP's code.
Normally I do this with range names and gotos to move imported data from one spot to another... then I found the transferworksheet method and YEA done on the export!
Nov 9 '12 #8

NeoPa
Expert Mod 15k+
P: 31,494
Zmbd:
then I found the transferworksheet method and YEA done on the export!
Now you're confusing me Z. This is about identifying which cell in row 1 is the first blank one from the left. My thought was to use the Ctrl-Arrow approach which skips from one end of a block of cells to the other. Clearly there's no call for skipping across if A1 is already a free cell. If A1 has data but is only one cell wide (IE. B1 is empty) then Ctrl-RightArrow takes it across to the next block (which does us no good), so we have to allow for that.

Hence post #3 which lays out all the necessary steps in order to be guaranteed to find the first empty cell of the row.
Nov 9 '12 #9

zmbd
Expert Mod 5K+
P: 5,397
Let's see what OP has for us to work with... between us we've given the information needed to solve the question.
Besides, it strikes me as an homework question the more I read it.
Nov 9 '12 #10

Rabbit
Expert Mod 10K+
P: 12,366
I see now, I forgot that if it starts near a blank cell, it jumps to the very end of the spreadsheet.
Nov 9 '12 #11

NeoPa
Expert Mod 15k+
P: 31,494
Indeed. If the cell to the right is blank then it will go to the next non-blank cell on its right. If there are no more in the row then it will go to the far-right cell of the row.

This behaviour can be seen and experimented with by using the Ctrl key with one of the arrow keys or else pressing the End key followed by one of the arrow keys.
Nov 9 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.