Finding TWO things

[kirkm]

Hi

I got this code example form Excel Help

--
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 know this finds the value 2 and changes it to 5. Could this
be changed so it finds either 2 OR 3 ?

I don't need to change anything to 5, just want to build an
array of row numbers where there's 2 or 5.

(The 2 and 5 here are examples, eventually I want to find any two
strings with this method).

Thanks for any help.
Cheers - Kirk

 

[Patrick Molloy]

thats why we use parameters...

Option Explicit
Sub Demo()
Setto5 2
Setto5 3
End Sub



Sub Setto5(val As String)
Dim c As Range
Dim firstAddress As String
With Worksheets(1).Range("a1:a500")
Set c = .Find(val, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext()
Loop While Not c Is Nothing
End If
End With
End Sub




in message news:[email protected]...

 

[kirkm]

thats why we use parameters...

Option Explicit
Sub Demo()
Setto5 2
Setto5 3
End Sub

Ah, yess.. but I was hoping to avoid going through the routine twice.

I wonder - probably a silly question - can you use an SQL query in
Excel e.g. with a criteria Like *2* or Like *3* ?


Thanks - Kirk

 

[keiji kounoike]

This is not so efficient way, because of checking every cells. but try
Sub test below. Sub test below calls FindRowNum that returns a array of
row's number satisfying criteria(*2* or *3*) in the range("A1:A500").

FindRowNum is a function to return a array of row's number satisfying
criteria *2* or *3* within some range.

Sub test()
Dim v As Variant
v = FindRowNum(Range("A1:A500"))
For i = 0 To UBound(v)
MsgBox v(i)
Next
End Sub

Function FindRowNum(rng As Range) As Variant
Dim arr() As Long, i As Long
Dim R As Range

ReDim arr(rng.Cells.Count - 1)

For Each R In rng
If R.Value Like "*2*" Or R.Value Like "*3*" Then
arr(i) = R.Row
i = i + 1
End If
Next
ReDim Preserve arr(i - 1)
FindRowNum = arr
End Function

Keiji