Macro for copying and pasting value onto the next empty cell

[Miguel Freire]

Hey, everyone!

Im a real zero when it comes to macro and, actually, any type of programming, so i was looking for some help...


Well, i was in need of a macro that would make all the cells inside my selection "skip" onto the next empty cell. This mustn't use any cut and paste, since this seems to cause the other formulas in the spreadsheet to become broken. Also, this needs to be ciclical, so that, if a value is on the last (top-down) cell of a selection, it will jump to the first empty cell on that selection. this should work across multiple columns too. Oh, and last thing, there are some cells, which on my spreadsheet are colored, which i wish to Always stay in place and should not be moved by the macro. =P


For example, on column A, lets say i have the following values, 0 being an empty cell.


A1 - 0

A2 - 3

A3 - 5 - colored

A4 - 0

A5 - 4

A6 - 0


After using the macro, this should be:


A1 - 0

A2 - 0

A3 - 5

A4 - 3

A5 - 0

A6 - 4


Thanks in advance!

 

[SirJB7]

Hi, Miguel Freire!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


A few questions:

a) It will be run on manually selected ranges?

b) If range is a matrix instead of a vector, should it be processed row-col wise or col-row wise?

c) Should it be processed downwards always? As upwards will always generate a different output.

d) All cells with any background are unmovable?

e) If moved cells contain formulas, should they maintain it or just its values?

f) What about title rows? How to identify them?

g) What about formulas with totals (e.g., values in A2, A4, A5, in A6=SUM(A2:A5)?


For the time being I have no more questions that raised automatically.


Despite of this, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!

 

[Miguel Freire]

Hey, SirJB7! Wow, thanks for replying so fast :D


I've already tried searching for a few diferent keywords, no success, though


Heres a sample of the spreadsheet, which also includes how the sheet should look after runing the macro once: https://docs.google.com/spreadsheet/ccc?key=0AmzCLdcnGsn3dFQ5ZGZXM2NpUXQzaHZWcXp1cFJsMnc&usp=sharing


This is a spreadsheet for alocating interns to diferente Jobs. Each of the colored cells represents one job,and each column represent one day of the week. The two white cells underneath each job represent the two weeks which an intern should spend at each job, so that if the intern is at the fisrt cell, this means that he is in his first week and, if he is in the second cell, his second week. After finishing a "cycle", the intern should be returned to the first job on that given day. I've entered temporary placeholder names, havent gotten around to actually allocating each intern.


Anyway, lets get to answering your questions:


a)it should run on manually selected ranges

b)if i get this right, it should be processed row-col wise

c) It should always be processed downwards, except when the intern reaches the last job on that weekday, when he should be returned to the first job of that day.

d) yup, all cells with backgrounds should be unmovable

e) at first, this spreadsheet in particular wouldnt contain any formulas, it just serves as the base for other spreadsheets. But i believe it would be best that, if the moved cells have formulas, those formulas should be moved too.

f) The titles rows are identified on the top of the sheet with the days of the week.

g) this shouldnt be an issue, i think, since the spreadsheet only uses names


Again, thank you very, very much!

Best reagards!

 

[SirJB7]

Hi, Miguel Freire!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Macro%20for%20copying%20and%20pasting%20value%20onto%20the%20next%20empty%20cell%20-%20Teste%20%28for%20Miguel%20Freire%20at%20chandoo.org%29.xlsm


The cyan button calls this macro:

-----

[pre]

Option Explicit

Sub Shifting()
' constants
Const ksWS = "Sample"
Const ksWeekly = "WeeklyTable"
Const klNoInterior  As Long = 2 ^ 24 - 1
' declarations
Dim rngW As Range, rngD As Range, rngS As Range
Dim iShifted() As Integer
Dim I As Integer, J As Integer, K As Integer, L As Integer
Dim A As String, bOk As Boolean
' start
Set rngW = Worksheets(ksWS).Range(ksWeekly)
If Selection.Cells.Count > 1 Then
Set rngS = Application.Intersect(rngW, Selection)
Else
Set rngS = rngW
End If
If rngS Is Nothing Then Exit Sub
' process
For I = 1 To rngS.Columns.Count
' one column range
Set rngD = rngS.Columns(I)
With rngD
' empty spaces
bOk = False
For J = 1 To .Rows.Count
If .Cells(J, 1).Interior.Color = klNoInterior And _
.Cells(J, 1).Value = "" Then
bOk = True
Exit For
End If
Next J
If bOk Then
' shifted control
ReDim iShifted(.Rows.Count)
For J = 1 To .Rows.Count
iShifted(I) = 0
Next J
' column
For J = 1 To .Rows.Count
If .Cells(J, 1).Interior.Color = klNoInterior And _
.Cells(J, 1).Value <> "" And _
iShifted(J) = 0 Then
' non colored, non empty, non shifted
K = J
bOk = False
Do Until bOk
K = K + 1
L = (K Mod .Rows.Count)
If L = 0 Then L = L + .Rows.Count
If .Cells(L, 1).Value = "" And .Cells(L, 1).Interior.Color = klNoInterior Then
.Cells(L, 1).Formula = .Cells(J, 1).Formula
.Cells(J, 1).Value = ""
bOk = True
iShifted(L) = J
End If
Loop
End If
Next J
End If
End With
Set rngD = Nothing
Next I
' end
Set rngS = Nothing
Set rngW = Nothing
Beep
End Sub

[/pre]
-----


If only one cell is selected it runs over the whole week and rows, if more than one cell is selected it only runs over those cells.


Just advise if any issue.


Regards!

 

[Miguel Freire]

WOW! This Works perfectly! Thank you so very much!

 

[SirJB7]

Hi, Miguel Freire!

Glad you solved it. Thanks for your feedback and nd welcome back whenever needed or wanted.

Regards!