Find text, replace it but now I need to change the height of allpreceding/following text

[ezduzitez]

Hi all-

Your help will be greatly appreciated on this issue.

I have this macro that does what I need it to do, but I'm having to change the size of text before and after this custom symbol. The text size should be 11 before and after the symbol.

I'm attaching my current macro for your review. Thanks in advance.

Sub Surfacefinish()
With Application.ReplaceFormat.Font
.Name = "IMS"
.Size = 14
.Superscript = False
.Subscript = False
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Cells.replace What:="125 SURFACE FINISH", Replacement:="«", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=True
End sub

Sincerely,

EZ

 

[Claus Busch]

Hi,

Am Fri, 26 Apr 2013 06:42:24 -0700 (PDT) schrieb (e-mail address removed):

Sub Surfacefinish()
With Application.ReplaceFormat.Font
.Name = "IMS"
.Size = 14
.Superscript = False
.Subscript = False
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Cells.replace What:="125 SURFACE FINISH", Replacement:="«", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=True
End sub

try (modify the range to suit):

Sub Surfacefinish()
Dim rngC As Range
Dim Start As Integer

Application.ScreenUpdating = False
For Each rngC In Range("A1:A100")
rngC = Replace(rngC, "125 SURFACE FINISH", "«")
Start = InStr(rngC, "«")
With rngC
With .Font
.Name = "IMS"
.Size = 11
End With
.Characters(Start, 1).Font.Size = 14
End With
Next
Application.ScreenUpdating = True
End Sub

Regards
Claus Busch

 

[ezduzitez]

Thanks Claus. I like the way that works. I can use it on other files.

My bad. I failed to specify crucial information for my issue. I'm using different fonts and symbols from different companies to accomplish the desiredresults.

What I need is to find "125 SURFACE FINISH", replace it with "«" and thenwithin that active cell change the text size only within the active cell before and after the "«" symbol. Then find the next instance of "125 SURFACE FINISH" and repeat the cycle until the end of the file.

Your help is greatly appreciated

EZ

 

[Claus Busch]

Hi,

Am Fri, 26 Apr 2013 12:00:43 -0700 (PDT) schrieb (e-mail address removed):

What I need is to find "125 SURFACE FINISH", replace it with "«" and then within that active cell change the text size only within the active cell before and after the "«" symbol. Then find the next instance of "125 SURFACE FINISH" and repeat the cycle until the end of the file.

then test whether 125 SURFACE FINISH is in the cell and only in this
case will the font and the size changed:

Sub Surfacefinish()
Dim rngC As Range
Dim Start As Integer

Application.ScreenUpdating = False
For Each rngC In Range("A1:A100")
If InStr(rngC, "125 SURFACE FINISH") > 0 Then
rngC = Replace(rngC, "125 SURFACE FINISH", "«")
Start = InStr(rngC, "«")
With rngC
With .Font
.Name = "IMS"
.Size = 11
End With
.Characters(Start, 1).Font.Size = 14
End With
End If
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus Busch

 

[ezduzitez]

I know enough to get by on what I've needed done so far but I don't know what to do on this error I'm getting "Compile error: Expected End Sub". I call two macros prior to this one and then I get this error when Surfacefinishmacro comes up

Thanks again
________________________________________

Sub AutoFix()


Call Discus_Fix
Call SelectCellRangeA5
Call Surfacefinish
'
' Go home
'

'
Range("A22").Select
_________________________________________________________

End Sub

Sub Surfacefinish()
Dim rngC As Range
Dim Start As Integer

Application.ScreenUpdating = False
For Each rngC In Range("D1100")

If InStr(rngC, "125 SURFACE FINISH") > 0 Then

rngC = replace(rngC, "125 SURFACE FINISH", "«")
Start = InStr(rngC, "«")
With rngC
With .Font
.Name = "IMS"
.Size = 11
End With
.Characters(Start, 1).Font.Size = 14
End With

End If

 

[Claus Busch]

Hi,

Am Tue, 30 Apr 2013 10:25:49 -0700 (PDT) schrieb (e-mail address removed):

Sub Surfacefinish()
Dim rngC As Range
Dim Start As Integer

Application.ScreenUpdating = False
For Each rngC In Range("D1100")

If InStr(rngC, "125 SURFACE FINISH") > 0 Then

rngC = replace(rngC, "125 SURFACE FINISH", "«")
Start = InStr(rngC, "«")
With rngC
With .Font
.Name = "IMS"
.Size = 11
End With
.Characters(Start, 1).Font.Size = 14
End With

End If

End Sub
^^^^^^ is missing



Regards
Claus Busch

 

[ezduzitez]

Really appreciate your support Claus,

I did that and now I get this "Compile error: For without Next" and the "End Sub" is highlighted.

 

[Claus Busch]

Hi again,

Am Tue, 30 Apr 2013 10:48:06 -0700 (PDT) schrieb (e-mail address removed):

Really appreciate your support Claus,

Sub Surfacefinish()
Dim rngC As Range
Dim Start As Integer

Application.ScreenUpdating = False
For Each rngC In Range("D1100")

If InStr(rngC, "125 SURFACE FINISH") > 0 Then

rngC = replace(rngC, "125 SURFACE FINISH", "«")
Start = InStr(rngC, "«")
With rngC
With .Font
.Name = "IMS"
.Size = 11
End With
.Characters(Start, 1).Font.Size = 14
End With
End If

Next
Application.ScrennUpdating=True
End Sub


Regards
Claus Busch

 

[ezduzitez]

Thanks again Claus. I now get this "Run-time error '438': Object doesn't support this property or method" and when I click on "Debug" it highlights "Application.ScrennUpdating=True"

 

[Claus Busch]

Hi,

Am Wed, 1 May 2013 06:51:24 -0700 (PDT) schrieb (e-mail address removed):

Thanks again Claus. I now get this "Run-time error '438': Object doesn't support this property or method" and when I click on "Debug" it highlights "Application.ScrennUpdating=TrueApplication.ScrennUpdating=True"

sorry, that is a typo. Change it to:
Application.ScreenUpdating=True

The correct code you see in my second answer.


Regards
Claus Busch

 

[ezduzitez]

Got the job done! Works great! Your help is greatly appreciated Claus.

Sincerely,

EZ