May 7th, 2013, 04:43 PM
I had a macro which finds the 12th character in between the text from 2nd tab to 3rd tab and find the space “ “ reversely and replace the space “ “ with “~~~”
But if the para doesn’t contain tab, the macro fails to run. The macro stuck and throws the "Subscript out of range" Error
How to fix it? i.e., If the para doesn’t contain tab (if the condition is false) the macro should skip to the next para.
Sample file attached for your reference
Dim Para As Paragraph, Rng As Range, i As Long
Dim StrOut As String, StrTmp As String
For Each Para In .Paragraphs
If Len(Split(Para.Range.Text, vbTab)(2)) > 10 Then
StrOut = ""
Set Rng = Para.Range
Rng.End = Rng.End - 1
For i = 0 To UBound(Split(Rng, vbTab))
If i <> 2 Then
StrOut = StrOut & Split(Rng, vbTab)(i) & vbTab
StrTmp = Left(Split(Rng, vbTab)(i), 12)
StrTmp = Left(StrTmp, InStrRev(StrTmp, " ") - 1) & "~~~"
StrTmp = StrTmp & Mid(Split(Rng, vbTab)(i), Len(StrTmp) - 1, Len(Split(Rng, vbTab)(i)))
StrOut = StrOut & StrTmp & vbTab
Rng.Text = Left(StrOut, Len(StrOut) - 1)
Thanks in advance,
Click Here to Expand Forum to Full Width
This is a Codeguru.com survey!