You need to define the addresses as a range something like this...

with sheets("Sheet1")
tmpName = Left(.Range("B1"), 3) & " " & Right(.Range("B1"), 2)
end with
MsgBox tmpName

As a complete aside you should have Option Explicit at the top of your
module. Oddly enough it would catch this error for you as you would end up
with undeclared varaibles that would not compile... If you have more
questions about that just ask...
--
HTH...

Jim Thomlinson


"Ronbo" wrote:

What is the VBA routine that is the equivalent to the formula;

= Left(B1, 3) & " " & Right(B1, 2)

When I use:

tmpName = Left(B1, 3) & " " & Right(B1, 2)
MsgBox tmpName

In works without an error, but the message box is empty?

As always, any help is appreciated.

Regards
Ronbo