How to copy from cell into next empty cell and loop through
Category: [Excel] Demo Available
How to copy from cell into next empty cell and loop through
Requirement:
The user is trying to find a way to automate the allocation of work en masse and this could potentially save an hour a day.
The user wants to know if there is a way to automatically copy and paste from a cell in one column to the next empty cell in another working down the list of cells to copy from.
As an example, using the sample spreadsheet attached. The user would like to say copy cell H2 and paste it into D3, then the user would like to copy cell H3 and copy into D6 and so on working down the list of people in column H.
There may be more empty columns than people in Column H so there may be a need to loop through and start again.
The code/formula would need to take into account the already existing formula in Column D.
Solution:
Code:
Option Explicit
Sub Shezmo()
Dim i As Long, lr As Long, lrT As Long, j As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
lrT = Range("H" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
j = 2
For i = 2 To lr
If Range("D" & i) = "" Then
Range("D" & i) = Range("H" & j)
j = j + 1
If Range("H" & j) = "" Then j = 2
End If
Next i
Application.ScreenUpdating = True
MsgBox "Completed"
End Sub
With slight changes
Code:
Sub Maybe_B()
Dim c As Range, j As Long
j = 2
For Each c In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Offset(, 2)
If c.Value = "" Then c.Value = Cells(j, 8).Value: j = j + 1
Next c
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by AlanSidman and Jolivanes.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use Excel VBA to delete rows in a column based on a range of cells in another column |
| How to count and delete duplicate entries over multiple columns |
| How to delete rows based on cell content |
| How to use a macro to auto delete file when passed 15 days |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.