CODEWITHSUNDEEP
excelvba
user1996971
user1996971

Reputation: 543

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

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.

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.

I have written the following macros for correcting these frameshifts.

The first is just to determine if the two columns match in a particular row

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

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.

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.

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

And for the other

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

With these Macros written, I've been carying out the following steps.

  1. Hit CTRL+ M to call up the list of TRUE or FALSE for the title rows matching up
  2. Seek out a row with FALSE. Determine if it is four cells from dataset 1 or dataset 2 that needs to be removed
  3. Hit CTRL + M again, and have that row corrected.

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."

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.

Thanks in advance.

Upvotes: 0

Views: 216

Answers (1)

SeanC
SeanC

Reputation: 15923

a for/next or for/each loop should suit your needs.

as a quick throw together, I would do something like this, to run after your matching:

Dim I as range
for each I in Range("I2:I40028").Cells
     I.select
     if activecell.offset(0,-5)=activecell(1,-6) then 
         bluecut
     else
         redcut
     end if
next

of course, I would do all the testing internally, rather than selecting each cell, as it would run a lot faster, even with application.ScreenUpdating=False to stop having to view all the screen updates

Upvotes: 1

Related Questions