Databases
StackOverflow2013
Postsdbo
POI have three macros written for Excel in VBA; I'm looking for help to tie them together.
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POI have three macros written for Excel in VBA; I'm looking for help to tie them together.
    primarykey
    data
    text
    <p>I'm fairly new to coding so I'll be brief. I have a very large dataset to process in excel. From two spreadsheets, I've pooled data together. </p> <p>So each set has 4 columns. One column contains the alphanumeric names for a datapoint. (Example, NC000023, NC000224, etc.). I want to line up the matching alphanumeric names with one sheet to another. The first thing that came to mind was to SORT both columns alphabetically, which did match most up. However, there are some datapoints missing from one sheet to another, causing several frameshifs throughout the data. </p> <p>I have written the following macros for correcting these frameshifts. </p> <p>The first is just to determine if the two columns match in a particular row</p> <pre><code>Sub Matching() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+m ' Range("I2").Select ActiveCell.FormulaR1C1 = "=EXACT(RC[-4],RC[-5])" Range("I2").Select Selection.AutoFill Destination:=Range("I2:I40028"), Type:=xlFillDefault </code></pre> <p>This brings up a new column with TRUE of FALSE depending on whether or not D3 mathes E3, and D550 matches D550 and so forth. </p> <p>I then seek out any cells in the I column with FALSE, and determine which 4 cells I need to delete in order to correct that matching. The code was designed to cut out the culprit, paste it out of the way, and move the lower rows up to take its place.</p> <pre><code>Sub RedCut() ' ' Macro2 Macro ' Shortcut CTRL + r ' ActiveCell.Offset([0], [-3]).Select Range(ActiveCell, ActiveCell.Offset(0, 3)).Cut ActiveCell.Offset([0], [12]).Select ActiveSheet.Paste ActiveCell.Offset([0], [-12]).Select Range(ActiveCell, ActiveCell.Offset(0, 3)).Select Selection.Delete Shift:=xlUp End Sub </code></pre> <p>And for the other</p> <pre><code>Sub Bluecut() ' ' Bluecut Macro ' ' Keyboard Shortcut: Ctrl+b ActiveCell.Offset([0], [3]).Select Range(ActiveCell, ActiveCell.Offset(0, -3)).Cut ActiveCell.Offset([0], [9]).Select ActiveSheet.Paste ActiveCell.Offset([0], [-9]).Select Range(ActiveCell, ActiveCell.Offset(0, -3)).Select Selection.Delete Shift:=xlUp ' End Sub </code></pre> <p>With these Macros written, I've been carying out the following steps.</p> <ol> <li>Hit CTRL+ M to call up the list of TRUE or FALSE for the title rows matching up</li> <li>Seek out a row with FALSE. Determine if it is four cells from dataset 1 or dataset 2 that needs to be removed</li> <li>Hit CTRL + M again, and have that row corrected.</li> </ol> <p>My question is, is it possible to write a code that says "Perform Sub Bluecut for a row with FALSE in column I if the corresponding cell in column D is identical to one cell down and to the left, or Perform Sub Redcut for a row with FALSE in column if the corresponding cell in column D is identical to one cell up and to the right."</p> <p>Sorry, this seemed a lot less complicated to explain when I started typing. I'd appreciate ANY help you can give me, even if it is a tip to writing even a tiny piece of this code.</p> <p>Thanks in advance. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    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. This table or related slice is empty.
    1. COyou can use `Call routineX` to run a specific routine from say routineY. Looks like you'll need to change your `Sub`s into `Functions` so that they can accept parameters ...so they know which row to target.
      singulars
      1. POI have three macros written for Excel in VBA; I'm looking for help to tie them together.
      1. USwhytheq
    2. COThanks! However, instead of getting a particular value, all I want to do is cut and paste the targeted cells based on their Boolean value in column I. Would a function, rather than a Sub, allow me to do this? as the output will not be a value, so to speak?
      singulars
      1. POI have three macros written for Excel in VBA; I'm looking for help to tie them together.
      1. USuser1996971
    3. COdon't use a function, a sub should be able to accept parameters
      singulars
      1. POI have three macros written for Excel in VBA; I'm looking for help to tie them together.
      1. USDan
 

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