Use a function and pass the variables that are required. In the
function set the return value back to to function name.


Sub ConsCodeV3()
Dim c As Range
For Each c In Range("E1:E20")
If c < "CONS" Then
c.Offset(, -3) = UCase(c.Offset(, -2))
ColB = c.offset(,-3)
ColL = c.offset(,+7)
ColO = c.offset(,+10)
c.Offset(, -4) = NameSplit(ColB,ColL,ColO)
End If

If c = "CONS" Then
c.Offset(, -3) = UCase(c.Offset(, -2))
End If
Next c
End Sub

Function NameSplit(ColB,ColL,ColO)
Dim rng As Range

NameSplit = left(ColB,4) & Coll & ColO
End Function


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170411

Microsoft Office Help