Joseph.Marro
Board Regular
- Joined
- Nov 24, 2008
- Messages
- 153
Hello,
I have a workbook with three worksheets: “Working File”, “Standard Abbreviations” and “Approved Abbreviations”
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
In the “Working File” I have a nomenclature field. The field will contain the description of an item.
<o></o>
EXAMPLE: VALVE BALL-TYPE PRESSURE CONTROL SEAWATER FLANGED 8 INCH
<o></o>
The “Standard Abbreviations” worksheet contains two columns, “A” and “B”. Column “A” is the full word to be found in the nomenclature string in the “Working File” and column “B” is the abbreviation. This work sheet does have column headers.
<o></o>
EXAMPLE: VALVE = VLV
<o></o>
The “Approved Abbreviations” work sheet is set up exactly like the “Standard Abbreviations” but contains a different list of words.
<o></o>
EXAMPLE: SEAWATER = SW
<o></o>
I would like the user to have the ability to select a cell or a range of cells and click a button to apply abbreviations.
<o></o>
Here is the tricky part, when the user clicks the button to apply abbreviations I want the “Standard Abbreviations” to be applied first and then evaluate the length of the modified nomenclature. If the new nomenclature is greater than 48 characters in length I want the macro to ask them if they want to apply the “Approved Abbreviations”. If they click “No” they exit the macro. If the click “Yes” I want the “Approved Abbreviations” to be applied from right-to-left until the string is less than 48 characters long.
<o></o>
Is this possible… or should I say plausible?
<o></o>
This is the code I have to do a simple find and replace based on one table. I know it isn't very pretty.
<o></o>
Thank you,
Joseph Marro
I have a workbook with three worksheets: “Working File”, “Standard Abbreviations” and “Approved Abbreviations”
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
In the “Working File” I have a nomenclature field. The field will contain the description of an item.
<o></o>
EXAMPLE: VALVE BALL-TYPE PRESSURE CONTROL SEAWATER FLANGED 8 INCH
<o></o>
The “Standard Abbreviations” worksheet contains two columns, “A” and “B”. Column “A” is the full word to be found in the nomenclature string in the “Working File” and column “B” is the abbreviation. This work sheet does have column headers.
<o></o>
EXAMPLE: VALVE = VLV
<o></o>
The “Approved Abbreviations” work sheet is set up exactly like the “Standard Abbreviations” but contains a different list of words.
<o></o>
EXAMPLE: SEAWATER = SW
<o></o>
I would like the user to have the ability to select a cell or a range of cells and click a button to apply abbreviations.
<o></o>
Here is the tricky part, when the user clicks the button to apply abbreviations I want the “Standard Abbreviations” to be applied first and then evaluate the length of the modified nomenclature. If the new nomenclature is greater than 48 characters in length I want the macro to ask them if they want to apply the “Approved Abbreviations”. If they click “No” they exit the macro. If the click “Yes” I want the “Approved Abbreviations” to be applied from right-to-left until the string is less than 48 characters long.
<o></o>
Is this possible… or should I say plausible?
<o></o>
This is the code I have to do a simple find and replace based on one table. I know it isn't very pretty.
<o></o>
Code:
[FONT=Calibri][SIZE=3]Option Compare Text[/SIZE][/FONT]
Opt[FONT=Calibri][SIZE=3]ion Explicit[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]Public Sub FindReplace()[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim WS As Worksheet[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim R_Find As Range[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim R As Range[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim i As Long[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim strFind As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim strReplace As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim strTarget As String[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]On Error GoTo Err_FindReplace[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]Set WS = Worksheets("Sheet2")[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]i = WS.Cells(Rows.Count, "A").End(xlUp).Row[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set R_Find = WS.Range("A2:A" & i)[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]For Each R In R_Find[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] strFind = R.Value[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] strReplace = R.Offset(0, 1).Value[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] ActiveCell.Value = Replace(ActiveCell.Value, strFind, strReplace)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Next[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]Exit_FindReplace:[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set WS = Nothing[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set R_Find = Nothing[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set R = Nothing[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Exit Sub[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Err_FindReplace:[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Resume Exit_FindReplace[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Resume[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri]End Sub[/FONT]
Thank you,
Joseph Marro