0

I've been trying to create code to compare entries in a list from one workbook to two lists on another workbook (a master list). The end goal is to have a macro which would run and mark if an entry in the list is found in one of the two master lists, called "Statistical" and "Non-statistical" (sheets 1 and 2 respectively) and then labelled accordingly. In order to do this, I need to define the two ranges of the master list workbook as variables for use in the Range.Find method, or any other method which would work.

The lists are set up as such:

  1. In the original list which is having a check run against it (and also where the code resides), there is an output of typically around 150 entries, filtered (by pre-determined criteria) such so that only maybe 100 are visible, with the number changing for each day's output on a sheet labelled "Changes"
  2. The "Master Statistical List" workbook is set up with two sheets that I have another macro populating and adding entries to. Sheet1 is named "Statistical" and contains all entries that are deemed "Statistical" and Sheet2 is named "Non-statistical" and contains all entries deemed Non-statistical. The lists are in the thousands of rows, so I try to avoid using the .Select function.
  3. The two master lists are both stored in column A starting from A1, while the "Changes" list being checked starts from A2 (due to a header).

Here is what I have so far for just the range references code:

'Establish the list references
Dim MSL As Workbook 'Master Statistical List
Dim SP As Worksheet 'Statistical Properties
Dim NSP As Worksheet 'Non-statistical Properties
Dim TWS As Workbook 'This Worksheet - the original one in which this code resides

Set MSL = Workbooks.Open(Filename:="Filepath\Master Statistical List.xlsm")
Set SP = MSL.Worksheets("Statistical") 'Alternatively .Worksheets(1)
Set NSP = MSL.Worksheets("Non-statistical") 'Alternatively .Worksheets(2)
Set TWS = ThisWorkbook

'Establish the Range references
'Statistical Properties List
SP.Activate
Dim rngStat As Range
Set rngStat = Range("A1", .Range("A1").End(xlDown))

'check if it actually worked
rngStat.Select 'it seems to work
Range("B1").Select 'select random cell so we know if selecting it again actually works (I run through this line-by-line to debug)
TWS.Activate 'Lets go back to the current workbook and then see if it still works!
rngStat.Select 'Here be errors

I would be running similar Dim-set combos for both the "Changes" range and the Non-statistical one.

I've tried several different solutions, including using a With parameter as such:

With SP
    Set rngStat = Range("A1", SP.Range("A1").End(xlDown))
End With

Or putting SP in front of the .Range function as shown above, but nothing works once the Active sheet is changed.

Is there a way to set rngStat as being universally-referring to the list of entries from A1 of its sheet down to the final entry in such a way that it can be used in whatever loops or functions later on in the macro? It is important that the range not be to a set cell but rather the last entry in the column A, as the count of entries is always changing on the source sheet and increasing on the master list.

Community
  • 1
  • 1
W S
  • 1
  • 1
  • Not sure if it's a typo here, but in the `With SP` you should add the "anchor" to the `Range(),` ie `.Range(...)` Also in the main code, you anchor the `.Range("A1").End(xlDown)` to ...what? There's no `With` statement showing. – BruceWayne Aug 09 '17 at 21:15
  • It seems like you mostly need to start referring to each range explicitly, including its sheet. This will work regardless which sheet is active. In this example I also include a reference to the code workbook. You could drop that out if not relevant, but I leave it in in case workbook is also required to be explicitly referenced: `Set rng = Workbooks(ThisWorkbook.Name).Sheets("Sheet2").Range("A1", Workbooks(ThisWorkbook.Name).Sheets("Sheet2").Range("A1").End(xlDown))` – David Aug 09 '17 at 21:27

1 Answers1

-1

You can only use Range.Select when the Range you want to select is on the active worksheet.

In the code below, there are two workbooks: TestMaster.xlsm and TestSlave.xlsx and both are open. Both macros fail on their second masterRng.Select statement.

Option Explicit

Sub testRange_SameBook_DifferentSheets()
    Dim masterRng As Range
    Workbooks("TestMaster.xlsm").Worksheets("Sheet1").Activate
    Set masterRng = Range("A1:B5")
    masterRng.Select 'Works
    Worksheets("Sheet2").Activate
    masterRng.Select 'Fails: Run-time error 1004
End Sub

Sub testRange_DifferentBooks()
    Dim masterRng As Range
    Workbooks("TestMaster.xlsm").Worksheets("Sheet1").Activate
    Set masterRng = Range("A1:B5")
    masterRng.Select 'Works
    Workbooks("TestSlave.xlsx").Worksheets("Sheet1").Activate
    masterRng.Select 'Fails: Run-time error 1004
End Sub

In both macros, a Range object called masterRng is defined as cells A1:B5 on worksheet Sheet1 of workbook TestMaster.xlsm.

Whilst worksheet Sheet1 of TestMaster.xlsm remains the active worksheet the (first) masterRng.Select is executed successfully. However, once this condition changes the (second) call to the method fails.

In macro testRange_SameBook_DifferentSheets() the active worksheet is switched to a different worksheet in the same workbook as masterRng. In the second macro testRange_DifferentBooks() the active worksheet is in a different workbook.

I suspect most users who are relatively new to VBA stumble across methods such as Activate, Select, etc because that is what they get from the macro recorder. Generally, these methods are not a good idea - see this post.

DMM
  • 1,090
  • 7
  • 8