How to move cell contents using VBA

mike guest98 · Jul 9, 2018

I’m using a program that I’m trying to add to. I need to move the contents down one cell but I’m not sure how does the program move contents down from one cell to the next if the next cell is full without overwriting it and then move that one down. I’ve included a jpeg of my excel sheet.
I hope you can help.

I'm currently using the program below.

Code:

Sub do_it()
Dim n, sht As Worksheet, cell As Range, num, tmp, rngDest As Range

Set sht = ActiveSheet
n = sht.Range("A1")
For Each cell In sht.Range("D1:D12,A16:A31,D16:D31,G16:G31,J16:J31,M16:M31").Cells
  tmp = cell.Offset(0, 1).Value
  If cell.Value = n And tmp Like "*#-#*" Then
    'get the first number
    num = CLng(Trim(Split(tmp, "-")(0)))
    Debug.Print "Found a positive result in " & cell.Address
    'find the next empty cell in the appropriate row
    Set rngDest = sht.Cells(num, sht.Columns.Count).End(xlToLeft).Offset(0, 1)
    'make sure not to add before col L
    If rngDest.Column < 12 Then Set rngDest = sht.Cells(num, 12)
    cell.Offset(0, 1).Copy rngDest
    Exit For
  End If
Next
End Sub

I'm trying to have the program move all the number sets down in the column E1:E12.
I need the program to move the set of numbers (could be more than one set of numbers in the range) down to the following cell below it and increase the last number in the set (no limit to the count). So in the example of cell E1 (8-16) would move to E2 and become 8-17 (cell E1 would be blank when after the move). When sets of number are located in cell E12 they would move to E1 but still increase the last number and would go round and round (E1 to E12 and back). How does the program move contents down from one cell to the next if the next cell is full without overwriting it and then move that one down, if this makes sense. An example is cell E12, how does it move up to E1 if E1 is full but has to be moved? That’s it but it must be done using the above program.
Thanks so much for any help.

Logit · Jul 10, 2018

.
Paste in the Sheet Level Module :

Code:

Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Selection) Is Nothing Then
        Selection.Insert Shift:=xlDown
        Cancel = True
    End If
End Sub

mike guest98 · Jul 10, 2018

I'm not sure Sheet Level Module will work with my VBA program but I'll try it. It has to be put into the VBA program. I'll try it and get back to you on Saturday (so busy at work). Thanks for your help

How to move cell contents using VBA

mike guest98

New member

Attachments

Logit

Member

mike guest98

New member