VBA: Find Text In Range

[mouse88]

I am currently using Range.Find to get values from a report.

The report will contain several times in colum A and I want to grab a value next to each occurence of this time.

Any suggestions?

 

[pgc01]

HI

After the Range.Find() to get the first value, use Range.FindNext() to get the other values.

 

[mouse88]

How do I know when it has found the last one?

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] FindCells()
    [COLOR="Blue"]Dim[/COLOR] rng [COLOR="Blue"]As[/COLOR] Range, sFirstAddress [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]With[/COLOR] Sheets("Sheet1").Cells
        [COLOR="Blue"]Set[/COLOR] rng = .Find(2, LookIn:=xlValues)
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] rng [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
            sFirstAddress = rng.Address
            [COLOR="Blue"]Do[/COLOR]
                MsgBox rng.Value
                [COLOR="Blue"]Set[/COLOR] rng = .FindNext(rng)
            [COLOR="Blue"]Loop[/COLOR] [COLOR="Blue"]While[/COLOR] [COLOR="Blue"]Not[/COLOR] rng [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]And[/COLOR] rng.Address <> sFirstAddress
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]