Need help with macro in Excel

Question

I need a macro that moves A2 to B1 and which I can use to move A5 to B4, A8 to B7, etc.
I can't get it. Anyone who can help?
/hans peter

PS. VBA code in answer works fine (I suppose).

Answer 1

If you want to 'edit' one pair of cells (e.g A8, A9)

select the first cell (A8) and run the below vba macro

.

Sub Selection_Edit_Pair()

'## 09-11-2024 ##

Set r = Selection

With r

.Offset(, 1).Value = .Offset(1).Value

.Offset(1).Value = ""

End With

End Sub

Answer 2

Sure, I can give it another try for you. I have updated the code for you, please try the following macro:

Dim runCount As Long

Sub MoveCells()

    runCount = runCount + 1

    Dim sourceRow As Long

    Dim targetRow As Long

    sourceRow = 2 + (runCount - 1) * 3

    targetRow = 1 + (runCount - 1) * 3

    If Not IsEmpty(Cells(sourceRow, 1).value) Then

        Cells(targetRow, 2).value = Cells(sourceRow, 1).value

        Cells(sourceRow, 1).value = ""

    Else

        MsgBox "No cells need to be moved anymore."

    End If

End Sub

This time, the macro will keep track of the last moved cell, and the next time you run the macro, it will start from where it left off, rather than moving the entire sequence at once.

Please let me know if this works for you!

Have a nice day!

Thomas

Answer 3

Sorry, Thomas, I didn't receive a notification that I had received a reply. But thank you very much for the job.

I have tried the macro and it works perfectly fine in its form. It was probably not clear enough in my description that I wanted to be able to use the macro on one sequence at a time. As you have made it, all the sequences are moved at the same time. Will you try again?

/hans peter

Answer 4

It has been a while since I last heard from you. Have you had the chance to try the macro program I shared with you? Did it work as you expected? Please share the results with me!

Thomas

Answer 5

Hi Hans Peter Wiehen,

Thanks for your post in Microsoft Community.

Please try the following macro program:

Sub MoveValues()

    Dim lastRow As Long

    Dim i As Long

    lastRow = Cells(Rows.count, 1).End(xlUp).Row

    If lastRow >= 2 Then

        Cells(1, 2).value = Cells(2, 1).value

        Cells(2, 1).ClearContents

        For i = 5 To lastRow Step 3

            Cells(i - 1, 2).value = Cells(i, 1).value

            Cells(i, 1).ClearContents

        Next i

    End If

End Sub

Before running the macro:

After running the macro:

I hope the macro I shared works for you too, and I look forward to your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist