Find Code Needs Help

[Ange]

I have a table with two columns (Directory & Permissions), however, the
Rights (read, full control, etc) are part of my Permissions data.
(example below)

Directory Permissions
\\UMP\DEP_AMBC\ RUP-AMBC-CLINICS (Read)
\\UMP\DEP_AMBC\ SDIR-UMMCU-AMBC_ADMI (Full Control)
\\UMP\DEP_AMBC\ADMIN\ RUP-AMBC-CLINICS (Read)
\\\DEP_AMBC\ADMIN\ SDIR-UMMCU-AMBC_ADMI (Read&Modify&Delete&Add)


What I need to do is pull the Rights (read, etc) out of the Permissions
cell and put it into the next column. I thought I would simplify this
and just find the cell containing the specific Right and have that
Right entered into the next column (I can use find/replace code to
replace that part with "" later) so here's what I tried:

Sub TestRights()

Dim Row As Integer

Range("B2").Select
Row = ActiveCell.Row

Do Until Cells(Row, 2).Value = ""
If Cells(Row, 2).Find("Full Control") Then
Cells(Row, 3).Value = "Full Control"
Else
Row = Row + 1
End If
Loop

End Sub


Problem here is that I cannot get my Find code to work. I tried an
expanded version of it also that contained What, After, LookIn, LookAt,
SearchOrder, SearchDirection, etc. but that did not work either. Does
anyone have any other ideas or could possibly help with my messed up
code. I don't care if it's pretty, I just want it to be simple so I
can modify it later if needed.

Thanks,
Ange

 

[Stopher]

I have a table with two columns (Directory & Permissions), however, the
Rights (read, full control, etc) are part of my Permissions data.
(example below)

Directory Permissions
\\UMP\DEP_AMBC\ RUP-AMBC-CLINICS (Read)
\\UMP\DEP_AMBC\ SDIR-UMMCU-AMBC_ADMI (Full Control)
\\UMP\DEP_AMBC\ADMIN\ RUP-AMBC-CLINICS (Read)
\\\DEP_AMBC\ADMIN\ SDIR-UMMCU-AMBC_ADMI (Read&Modify&Delete&Add)


What I need to do is pull the Rights (read, etc) out of the Permissions
cell and put it into the next column. I thought I would simplify this
and just find the cell containing the specific Right and have that
Right entered into the next column (I can use find/replace code to
replace that part with "" later) so here's what I tried:

Sub TestRights()

Dim Row As Integer

Range("B2").Select
Row = ActiveCell.Row

Do Until Cells(Row, 2).Value = ""
If Cells(Row, 2).Find("Full Control") Then
Cells(Row, 3).Value = "Full Control"
Else
Row = Row + 1
End If
Loop

End Sub


Problem here is that I cannot get my Find code to work. I tried an
expanded version of it also that contained What, After, LookIn, LookAt,
SearchOrder, SearchDirection, etc. but that did not work either. Does
anyone have any other ideas or could possibly help with my messed up
code. I don't care if it's pretty, I just want it to be simple so I
can modify it later if needed.

Thanks,
Ange

Use the Split function to divde it up, seeing that your data seems to
be 2 seperate strings and the dump the second part of the array into
the next cell across.

 

[Stopher]

Use the Split function to divde it up, seeing that your data seems to
be 2 seperate strings and the dump the second part of the array into
the next cell across.

asdata = split(cells(row,2))
Cells(row,3) = asdata(1)

 

[Tom Ogilvy]

select the permissions column and do Data=>Text to Columns, Select
delimited in the first tab, then select space as the delimiter.

 

[Ange]

Thank you, Tom! That is exactly what I needed to do.
Ange

 

[Ange]

Okay, I spoke too soon. This worked on the items that did not have any
additional spaces in them such as (Read), but what do I do about the
ones that additional spaces like (Full Control) or (Read & Modify &
Delete & Add)?

Thanks,
Ange

 

[Tom Ogilvy]

Select the permissions column

do Edit=>Replace
what:=(
Replace With: "|("

now do the text to columns and type in the vertical line as the delimiter.

--
Regards,
Tom Ogilvy

Okay, I spoke too soon. This worked on the items that did not have any
additional spaces in them such as (Read), but what do I do about the
ones that additional spaces like (Full Control) or (Read & Modify &
Delete & Add)?

Thanks,
Ange

 

[Ange]

Thanks, Tom. That worked great! My co-worker did point out one
problem we still have. Some of our permissions have two types of
rights. For example, "(Read & Modify & Delete & Add)(Full Control)"
So what happens here is I end up with two columns for the rights. Do
you know of a way to join these? I tried "=C2&D2" in the next column
over but then I cannot get rid of these columns and just have one
column for the rights.

Thanks,
Ange

 

[Tom Ogilvy]

will the second one always be butt up against the first so there is no space

do Edit=>Replace
what: " ("
Replace With: "|("

so you don't get ")|(" and a split on the second one.

If there is a single space then before you do it do

do Edit=>Replace
what: ") ("
Replace With: (<==leave blank)

then

do Edit=>Replace
what: " ("
Replace With: "|("


of course you can record a macro to do this same operation by turning on the
macro recorder while you do it manually

in all cases, the "" are for clarity and would not be included.

 

[Tom Ogilvy]

If you do use the

=C2&D2
after you do it, select column E and do Edit=>Copy, then immediately
Edit=>Paste Special, then select Values . This will replace your formulas
with the value displayed and you can now delete the intermediate columns.

 

[Ange]

Thank you! That works perfectly. In fact, I was able to put it into a
macro and it looks great. Thanks again!!!
Ange