Entries in range that meet simple condition - return as range

  • Thread starter Thread starter dkmd_nielsen
  • Start date Start date
D

dkmd_nielsen

I know this must be simple. But I simply cannot find information as
to what I really want to do. We are talking about a VBA macro.

I have single column range. (It is non-contiguous, 5 block of 10
rows. I'm not sure it matters.) I would like to search that range
for entries where the offset(0,1) contains a value of 500 or greater.
This for finding scouts that have sold $500+. I would like the
results returned as a range or an array.

Ultimately what will happen is that the results lookup will be
transposed to a two-columnar form created by BSA. That range will be
propagated by scrolling through it inserting the results from the
previous lookup. In that way, neither the lookup or the propagation
code will not contain any details as to the structure of cells that
are being referenced.

How do I accomplish the search? Is there some range search method I'm
clueless about? Is it a Vlookup and what does that look like?
Seriously, I've been looking the better part of a day trying all kinds
of different things. Now I need your help.

Thanks,
dvn
 
Here is an example. We scan down a range (part of column A), looking at the
values in the next column. Each time we find a value greater than 499, we
capture the column A address:

Sub nielsen()
Dim r As Range, cell As Range, rFound As Range
Set r = Range("A1:A5,A10:A15")
Set rFound = Nothing
For Each cell In r
If cell.Offset(0, 1).Value > 499 Then
If rFound Is Nothing Then
Set rFound = cell
Else
Set rFound = Union(rFound, cell)
End If
End If
Next

If rFound Is Nothing Then
Else
MsgBox (rFound.Address)
End If
End Sub
 
Back
Top