Hi,

I have following code:
Sub auto()

Dim txt As String
Range("A3").Formula = "=mid(trim(a2),16,5)"
txt = Range("A3").Value

If txt = "TRIAL" Then
Application.Run ("text2colGL")
Else
If txt = "AR AG" Then
Application.Run ("txt2colAR")
Else
Application.Run ("text2col")
End If
End If

End Sub

How can I avoide placing the mid/trim function in cell A3.
If I use:
text = mid(trim(range("A2"),16,5)

it doesn't give me the same value.

Cheers,

Harold

Option Explicit
Sub Auto()

Dim txt As String
With ActiveSheet
txt = Mid(Application.Trim(.Range("a2").Value), 16, 5)
End With

If ucase(txt) = "TRIAL" Then
Call Text2ColGL
Else
If ucase(txt) = "AR AG" Then
Call Txt2ColAR
Else
Call Text2Col
End If
End If

End Sub

You don't need application.run to call a procedure.

And if you get more choices, you may want to use "select case".

Option Explicit
Sub Auto2()

Dim txt As String
With ActiveSheet
txt = Mid(Application.Trim(.Range("a2").Value), 16, 5)
End With

select case ucase(txt)
case is = "TRIAL"
Call Text2ColGL
case is = "AR AG"
Call Txt2ColAR
case else
Call Text2Col
end select

End Sub

It can make it easier to read the code later.


mohavv wrote:

Hi,

I have following code:
Sub auto()

Dim txt As String
Range("A3").Formula = "=mid(trim(a2),16,5)"
txt = Range("A3").Value

If txt = "TRIAL" Then
Application.Run ("text2colGL")
Else
If txt = "AR AG" Then
Application.Run ("txt2colAR")
Else
Application.Run ("text2col")
End If
End If

End Sub

How can I avoide placing the mid/trim function in cell A3.
If I use:
text = mid(trim(range("A2"),16,5)

it doesn't give me the same value.

Cheers,

Harold

--

Dave Peterson