Dave,
Thanks for the prompt reply. I was aware of both for each and find,
findnext. I was hoping to find a more powerful technique that might be
available to do "Find All". Your response suggests no such technique
exists. I shall probably code find, findnext as it makes it easier to
step through the code than using for each.

Incidentally, I had problems understanding the find example:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

I thought "Not c Is Nothing And c.Address < firstAddress" showed short-
circuit evaluation which is what I am used to in the language "c".
cf. <http://en.wikipedia.org/wiki/Short_circuit_evaluation. I've just
removed a boring description of that which is not relevant to the find
example.
The reason the code works is that "Not c Is Nothing" is always True.

I think the example should read:
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If
End With

I have an example where lack of short-circuit evaluation leads to
tortured code:
Set c = Columns(1).Find(What:=TimeFormat)
If c Is Nothing Then
Else
If c < 930 Then Peak = True
End If

With short-circuit evaluation, I could write:
Set c = Columns(1).Find(What:=TimeFormat)
If Not c Is Nothing and c < 930 Then Peak = True

However, removing short-circuit evaluation in VBA would probably break
too much extant code. ;)

Overcharged - should be Overcharge - is declared as a string as I intend
to output it as a string.

I can do "With .Cells(I, 7): .Value = .Value - Overcharge: End With"
and, with .Value having the value 4.8 and displayed as "£4.80" and
Overcharge being "-£0.90", the result is 5.7, displayed as "£5.70".
(Excel quietly converts the string variable.)

OTOH, to add two string currency variables, I find I must do:
Cost = Application.Text(CSng(Cost) + CSng(Overcharge), " £#,##0.00")

Doubtless, my understanding is flawed and I hope to be pointed at
something instructive. ;)

In message of Sat, 18 Dec 2010
06:48:51 in microsoft.public.excel.programming, Dave Peterson
writes
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.
;)


--
Walter Briscoe