Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
Ange wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
Stopher wrote: Ange wrote: 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. Sub TestRights() Dim Row As Integer Dim asdata Range("B2").Select Row = ActiveCell.Row Do Until Cells(Row, 2).Value = "" asdata = split(cells(row,2)) Cells(row,3) = asdata(1) Row = Row + 1 Loop End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
select the permissions column and do Data=Text to Columns, Select
delimited in the first tab, then select space as the delimiter. -- Regards, Tom Ogilvy "Ange" wrote in message ups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
Thank you, Tom! That is exactly what I needed to do.
Ange Tom Ogilvy wrote: select the permissions column and do Data=Text to Columns, Select delimited in the first tab, then select space as the delimiter. -- Regards, Tom Ogilvy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
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 wrote: select the permissions column and do Data=Text to Columns, Select delimited in the first tab, then select space as the delimiter. -- Regards, Tom Ogilvy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
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 "Ange" wrote in message ps.com... 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 wrote: select the permissions column and do Data=Text to Columns, Select delimited in the first tab, then select space as the delimiter. -- Regards, Tom Ogilvy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
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. -- Regards, Tom Ogilvy "Ange" wrote in message ps.com... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
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. -- Regards, Tom Ogilvy "Ange" wrote in message ps.com... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Code Needs Help
Thank you! That works perfectly. In fact, I was able to put it into a
macro and it looks great. Thanks again!!! Ange Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to find code | Excel Discussion (Misc queries) | |||
cannot find code | Excel Programming | |||
Help with this Find code please | Excel Programming | |||
VBA code 'find next' | Excel Programming | |||
Find Code | Excel Programming |