very urgent

[Guest]

how can i find 2 cells at the same time. like i want to
search if Cell in column "A" has "Singpaore" and Cell in
column "B" has "MAL"

thanks

 

[Melanie Breden]

how can i find 2 cells at the same time. like i want to

search if Cell in column "A" has "Singpaore" and Cell in
column "B" has "MAL"

if several agreements to be searched you can use the Advanced Filter in
Data | Filter | Advanced Filter.
Your List range incl. headings is maybe in A1:B50.
Then write e.g. within the range D1:E2 the headings and the filter criteria.
You can filter in same place or copy the result to another place.
Record this action with the Macro recorder, in order to receive the syntax for the VBA code.


--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Rob van Gelder]

I understand you can do MATCH using an array formula, or something along
those lines. Maybe someone else can provide that example, if you're in fact
after a worksheet formula.

Since it's a programming question, here's one approach using VBA:

Sub test()
Dim i As Long

With ActiveSheet
For i = 1 To 1000
If .Cells(i, 1).Value = "Singapore" And .Cells(i, 2).Value =
"MAL" Then
MsgBox "found at row " & i
Exit For
End If
Next
End With
End Sub

 

[Greg Wilson]

The following code assumes that there is potentially more
than one instance of Singapore and "MAL" to find. The
code is designed to be case insensitive and is designed to
work if there are leading and/or trailing spaces included
(i.e. will accomodate sloppy typing).

Sub FindSingapore()
Dim Rng1 As Range, Rng2 As Range
Dim C As Range

Set Rng1 = Columns("A")
For Each C In Rng1.SpecialCells(xlCellTypeConstants)
If LCase(Trim(C)) = "singapore" And _
LCase(Trim(C.Offset(, 1))) = "mal" Then
Set Rng2 = Range(C, C.Offset(, 1))
'Do something ...
'Next two lines just demo that cells identified
Rng2.Select
MsgBox Rng2.Address
End If
Next
End Sub

Regards,
Greg

 

[Don Guillett]

A subject line like "very urgent" is not only NOT necessary here as all are
treated as urgent but it is not productive for the archives.