Find all in vba

[Walter Briscoe]

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).row, _
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]

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???

 

[Walter Briscoe]

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 <[email protected]> of Sat, 18 Dec 2010
06:48:51 in microsoft.public.excel.programming, Dave Peterson

 

[Don Guillett Excel MVP]

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 <[email protected]> of Sat, 18 Dec 2010
06:48:51 in microsoft.public.excel.programming, Dave Peterson

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.

 

[Walter Briscoe]

In message <[email protected]
..com> of Sat, 18 Dec 2010 07:17:53 in microsoft.public.excel.programming

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.

[snip]

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.

Don,
Thanks for the reply. The REPLACE method of the Range type will not
serve.
I need to find cells which are a given offset from the matched cells.
REPLACE, internally, can be considered to find an array of matches and
apply a change to the elements of that array. I want a function which
will return that array. It seems no such array function has been
provided by Microsoft.

The filter VBA function almost does what I want. It searches an array; I
want to search a range. The Excel filter capability may define what I
want.

A custom autofilter or two may do the job.
Select all and insert in another sheet makes copies of the relevant data
available.
Is there another method for going through the matching rows.
Down arrow after the filtration was not recorded by the macro recorder.

 

[Dave Peterson]

I like this better:

Do
c.Value = 5
Set c = .FindNext(c)

if c is nothing then
exit do
end if

if c.address = firstaddress then
exit do
end if

Loop

For me, it makes more sense to me and is more robust. Bad things could take
place inside that do/loop that break the c.value (when c is nothing after the
next find).

======
I've stopped using Integers and Singles. They only offer places where I lose
accuracy or can cause overflow errors.

I'd still use As double and format the output anyway I wanted using VBA's
Format() (not application.text()). In fact, checking the value to see if it's
numeric seems to make sense to me, too.