If your data is small, you could loop through the range and look for the
matching values.

If your data is large, take a look at FindNext in VBA's help. It has an example
that you can use.

ps. Why is OverCharged declared as a string -- not a double???



On 12/18/2010 06:40, Walter Briscoe wrote:
I run Excel 2003.

In columns("G:H"), I have:
£8.00 £4.80
£8.00 -£0.90
£8.00 £2.70

I can identify that "-0.90" in VBA with
Dim Overcharge As String
Overcharge = Application.Min(Columns("G:H"))

I want to identify all the cells which have that value. My example shows
one, but there could be more.

In Excel itself, I can select columns G and H and do Edit/Find... or
Ctrl+F to get to the Find and Replace dialog, where I set Find What: to
£-0.90 and click "Find All" to get a list of matching cells.

When I record a macro doing this, it just records the selection.
Is there a method which would return an array of matches?
(I want to avoid the tedium of find and a findnext loop.)

P.S. in VBA, I can refer equivalently to columns("G") and columns(7).
I can also refer to columns("G:H").
Is there a numeric equivalent to columns("G:H")?
columns(7,8) does not work. ;(

I discount something like
dim maxrow as long
maxrow = application.max(cells(7,range(7,65536).end(xlUp).r ow, _
cells(8,range(8,65536).end(xlUp).row))
Range(cells(7,1), cells(8, maxrow))
I've just thrown that together and don't vouch for it doing as I intend.
;)

--
Dave Peterson