Databases
StackOverflow2013
Postsdbo
POLooking to loop two VBA macros together in Excel, but I think I'm just missing something very small
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLooking to loop two VBA macros together in Excel, but I think I'm just missing something very small
    primarykey
    data
    text
    <p>OK, so I've been pulling my hair out trying to get this right, but I feel as if the answer should be very straightforward! </p> <p>First, I have two macros written, we'll call these LeftCut and RightCut. These will cut out a row of four columns and paste them elsewhere in the sheet. The VBA code for these is </p> <pre><code> Sub RightCut() ActiveCell.Offset([0], [-1]).Select Range(ActiveCell, ActiveCell.Offset(0, -3)).Cut ActiveCell.Offset([0], [6]).Select Range(ActiveCell, ActiveCell.Offset(0, 3)).Select Selection.Insert Shift:=xlDown ActiveCell.Offset([0], [-6]).Select Range(ActiveCell, ActiveCell.Offset(0, -3)).Select Selection.Delete Shift:=xlUp End Sub Sub LeftCut Range(ActiveCell, ActiveCell.Offset(0, 3)).Cut ActiveCell.Offset([0], [10]).Select Range(ActiveCell, ActiveCell.Offset(0, 3)).Select Selection.Insert Shift:=xlDown ActiveCell.Offset([0], [-10]).Select Range(ActiveCell, ActiveCell.Offset(0, 3)).Select Selection.Delete Shift:=xlUp End Sub </code></pre> <p>Both of these work by themselves. Now, all I want to do is loop these together so if a certain condition is met, say, if the four left columns don't match the four right columns and one row needs to be cut out, then one of these two macros are called. </p> <p>Now, I have a pseudo code written for a Do While loop, but is this even close to what I'm looking for? The main problem will be that at some points in the worksheet, up to twenty of the rows need to be cut and paste, so I want the macros above to be used over and over until ActiveCell = ActiveCell.Offset(0,-1). Is this possible with a Do While loop??</p> <pre><code>Sub HighAce() Dim i As Long Dim ActiveCell As Range i = 2 Application.ScreenUpdating = True Do While i &lt;= 40043 Set ActiveCell = Range("E" &amp; i) If ActiveCell = ActiveCell.Offset([0], [-1]) Then ActiveCell.Offset([1], [0]).Select ElseIf ActiveCell &gt; ActiveCell.Offset([0], [-1]) Then Application.Run "'Methylation Array.xlsm'!NewBlueCut" ElseIf ActiveCell &lt; ActiveCell.Offset([0], [-1]) Then Application.Run "'Methylation Array.xlsm'!NewBlueCut" Else: Stop End If Loop End Sub </code></pre> <p>Am I on the right track here? Is there a line I'm missing? </p> <p>I appreciate any help anyone can provide. I'll come back to this problem later with a fresh mind, and I'll see if I can find a solution myself too! </p> <p>Thanks! </p> <p>EDIT: Sample dataset </p> <p>x x x A01 A01 x x x</p> <p>x x x A02 A04 x x x </p> <p>x x x A06 A05 x x x </p> <p>x x x A07 A06 x x x</p> <p>x x x A08 A09 x x x</p> <p>So if the A01 on the top right is the active cell, when ActiveCell=ActiveCell.Offset(0,-1) then move onto the next row. Here, since the Active Cell is > the adjacent cell, carry out Leftcut. Now, the Activecell &lt; the Adjacent cell, so carry out RightCut. Another rightcut will make these two cells equal, so the cursor will move to the next row, and go again. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1996971
    1. USuser1996971
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLooking to loop two VBA macros together in Excel, but I think I'm just missing something very small
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLooking to loop two VBA macros together in Excel, but I think I'm just missing something very small
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POLooking to loop two VBA macros together in Excel, but I think I'm just missing something very small
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou should iterate OR simply increase `i` inside loop, otherwise it'll be endless.
      singulars
      1. POLooking to loop two VBA macros together in Excel, but I think I'm just missing something very small
      1. USPeter L.
    2. COCan you show us sample data structure? I feel certain you won't need to macros for this...
      singulars
      1. POLooking to loop two VBA macros together in Excel, but I think I'm just missing something very small
      1. USAlex P
    3. COSee Edit for dataset.
      singulars
      1. POLooking to loop two VBA macros together in Excel, but I think I'm just missing something very small
      1. USuser1996971
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload