Find a string within a string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

El Bee - 25 Jan 2008 02:46 GMT

I have two columns that contain data in worksheet "Programs"

Access Level    Program Name
94 95 99    ITEMMGMT
27 99    ORDERMGT
95 97 99    PURCHMGT

In another worksheet (Data), same workbook, I have multiple rows of data
with in some of these rows will contain the program name above and one of the
1-3 access levels in the associated column in "Programs".

I need to find each row that has the corresponding program name in Cell D
and one of the 2-3 number values in Cell C.
If found then I need to copy that row to another worksheed (Profiles) using
the transpose option.

I have just discovered this link within the Excel discussion group and I'm
finding a wealth of information but unless I'm searching incorrectly I can't
find an example to work from.

Thanks for your help,

Larry

I want to sea

Reply to this Message

dbKemp - 25 Jan 2008 11:38 GMT

> I have two columns that contain data in worksheet "Programs"
>
[quoted text clipped - 21 lines]
>
> I want to sea

Try looking at InStr function (Returns a Variant (Long) specifying the
position of the first occurrence of one string within another.)

Reply to this Message

Joel - 25 Jan 2008 12:06 GMT

The code would be something like this

SearchData = "abc"
With Sheets("Data")
Set c = .Columns.Find(what:=SearchData, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Set CopyRange = .Range(.Cells(c.Row, "A"), .Cells(c.Row, "Z"))
CopyRange.Copy
Sheets("NewSheet").Range("A1").PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
End If

End With

> I have two columns that contain data in worksheet "Programs"
>
[quoted text clipped - 21 lines]
>
> I want to sea

Reply to this Message