Results 1 to 3 of 3

Select a cell at random based on multiple selection criteria across multiple sheets.

Threaded View

  1. #1
    Registered User
    Join Date
    02-03-2016
    Location
    Queensland, Australia
    MS-Off Ver
    2007
    Posts
    2

    Select a cell at random based on multiple selection criteria across multiple sheets.

    Hey all, new forum user here. I have bit of a problem I can't wrap my head around. I have to select a cell at random and delete its value, but the random cell I select has to meet some criteria:

    1. It has to be in the Range C3: L12 (in a worksheet called 'WellPlacement'),
    2. It has to be unpopulated (i.e. it can't already have a value > 0),
    3. It has to be within a close proximity (+/- 3 cells) to an already selected cell (within the range C3: L12),
    4. The equivalent cell in a separate worksheet (named 'ContaminantPlume') has to be populated. For example, if the selected cell in 'WellPlacement' is E5, then E5 in the Worksheet 'ContaminantPlume' must have a value > 0. If the cell E5 in ContaminantPlume = 0, then E5 in WellPlacement cannot be a candidate for the random cell selected by this function.

    I've figured out how to incorporate the first three criteria using the following code:

                                            Dim RNG1 As Range, r As Range, c As Collection
                                            Set c = New Collection
                                            Set RNG2 = Range(ActiveCell.Offset(-3, -3), ActiveCell.Offset(3, 3))
                                            Set RNG3 = Range("C3:L12")
                                            Set RNG1 = Intersect([RNG2], [RNG3])                                                              
                                            For Each r In RNG1
                                                If r.Value = 1 Then
                                                    c.Add r
                                                End If
                                            Next r
                                            N = Application.WorksheetFunction.RandBetween(1, c.Count)
                                            Set rselect = c.Item(N)
                                            rselect.Select
                                            ActiveCell.Value = ""
    It's the 4th criteria that I can't seem to work out. Any ideas?

    Thank you for your time! -Scott.
    Last edited by alansidman; 02-03-2016 at 09:42 AM. Reason: code tags added

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Merge multiple sheets data into one worksheet based on multiple criteria
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-05-2016, 10:32 AM
  2. [SOLVED] select random cells from multiple sheets and copy
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-10-2014, 06:24 PM
  3. Select Multiple Sheets Based on Cell Values VBA
    By ZachA3 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-21-2014, 04:32 PM
  4. Select multiple criteria based on check box selection and return all unique values.
    By TommyToe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 09:14 AM
  5. Multiple selection criteria, which formula? .. and selection based on unique numbers
    By FalkirkJim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2012, 05:22 PM
  6. [SOLVED] Select Random Records With Multiple Criteria
    By AlexRoberts in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-04-2011, 04:18 AM
  7. Random selection with multiple column criteria
    By kleick in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-03-2009, 03:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1