Find a string within a string
 

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

Find a string within a string
 

On Jan 24, 9:46 pm, El Bee wrote:
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

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

Find a string within a string
 

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

"El Bee" wrote:

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