Determine if String of Text is in a Cell 1

[Bass71]

I have the following code to iterate through ranges and determine if Variant i exists in any cell. For the most part, the cells have only one value, either matching i or not, but when there's more text in the cell than just i, I am sunk.

Dim rng As Range, r As Range, iCol As Integer, i As Variant

Set rng = Range([B2], [B2].End(xlDown))

i = InputBox("Type Text or Number to be Searched")

For Each r In rng
For iCol = 2 To 11
With Cells(r.Row, iCol)
If .Value = i Then...

 

[PHV]

If InStr(.Value, i) > 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

What exactly do you need this for? If we can get rid of the loop, it's generally for the better/imporovement.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[AnotherHiggins]

Consider auto-filtering for the string. You can use wildcards (*) to look for the string anywhere in a cell.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Bass71]

Zack,

It's to determine if certain texts exist in a matrix of varying rows by 197 cols. The column heading in which text extists is then pasted to a different cell. I wouldn't know how to begin without some kind of looping statement.

I appreciate the input.

-Rich O

 

[firefytr]

Something like that, yes, you'd almost surely need a loop, although not necessarily as large as maybe thought. You can use other features/functions to speed it up...

Sub CheckColumnsForValuePlease()

    Dim ws As Worksheet, wsPaste As Worksheet
    Dim rngFind As Range, rngCol As Range
    Dim iCol As Long, iRow As Long, i As Long
    
    Set ws = Sheets("Sheet1") 'sheet to look in
    Set wsPaste = Sheets("Sheet2") 'sheet to paste to
    iRow = 1 'set starting position
    iCol = 1 'set starting position
    
    For i = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        Set rngFind = Nothing
        Set rngFind = ws.Columns(i).Find("*value*")
        If Not rngFind Is Nothing Then
            ws.Cells(1, i).Copy wsPaste.Cells(iRow, iCol)
            'Incriment either one of these, or set as desired
            'I incrimented the columns by one
'            iRow = irwo + 1
            iCol = iCol + 1
        End If
    Next i
    
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Bass71]

Thanks for all your help! Question: Is there a definitive resource in hard copy form that lists all funtions/methods/objects in VBA? I find myself doing alot of cross-referencing and in my frenzy the solution was glossed over 10 times. I think something like that would be extremely helpful.

Thanks.........RO

 

[firefytr]

You could download the entire help file for Excel VBA Language Reference here ...

http://www.microsoft.com/downloads/...2e-4877-4563-8e83-4848dda796e4&DisplayLang=en

For a more detailed version, check out the table here ...

http://www.vbaexpress.com//resources/msdn_library_table.php#RANGE!A18

.. example, 2002 OM ...

http://msdn.microsoft.com/library/d.../vbaxl10/html/xltocObjectModelApplication.asp

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci