On Dec 18, 8:25*am, Walter Briscoe
wrote:
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- Hide quoted text -

- Show quoted text -

If your object is to replace all instances of the minimum then have
your macro determine the minimum and then use REPLACE to replace all
instances. Be careful that you don't replace something you don't want
to.