1

Apologies for the uninformed question but I've been looking into macros all morning to help me with a task I'm completing and I haven't found the answers that I need by searching google. I'm using windows 10 and excel 2016. Here's the macro I recorded:

Sub biweekly()
'
' biweekly Macro
'
' Keyboard Shortcut: Ctrl+w
'
    Rows("53:53").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A52:C52").Select
    Selection.Cut Destination:=Range("A53:C53")
    Range("M52").Select
    Selection.Cut Destination:=Range("M53")
    Range("D53").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"
    Range("D53").Select
    Selection.Copy
    Range("E53:L53").Select
    ActiveSheet.Paste
    Range("D53:L53").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("51:52").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A54").Select
End Sub

Essentially, I'm combining weekly rows into biweekly rows. I've done this before using pivot tables and getpivotdata but I was hoping for a more elegant solution this time. I would like the macro to loop once it's finished, moving down 3 rows to work on the next pair of weeks afterward (so, in this example, if the new row I created is now row 51 after deleting the individual weekly rows, I would like to move down to row 54 and insert a new row above it to begin working on the week row pairs of 52 and 53).

I don't know how to loop the macro so that it jumps down rows in each loop. I also would like to make a macro stop running conditionally, like when the value in column A changes (I have a large worksheet sorted alphabetically with names containing years of biweekly pay data, and I would like the macro to stop at the end of each name so that I can adjust the pay period concatenation and run it again for the new name). Alternatively, knowing how to make it run a certain number of times (say, 30) would be a good alternative.

Any help at all on any of these issues would be greatly appreciated. Again, apologies for being unfamiliar with macros and VBE.

0

Some help with loops is at How To Repeat Tasks With Your VBA Code by Looping. The simplest kind of loop would be For i = 1 to 30 at the beginning and Next i at the end.

For your other questions, try the Offset property:

  • To select a cell that is three rows below the current cell,
    Selection.Offset(3, 0).Select

  • To stop the macro if a cell is different than the cell three rows above,
    If Selection.Value <> Selection.Offset(-3, 0).Value Then Exit Sub

Recording a macro is a great start. When you become more familiar with VBA code, you will probably merge lines that end with .Select and begin with Selection..

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.