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