Hey Bob:

Thanks for those tips. I knew the solution had something
to do with how many rows there were in column D that had
entries, but I had no idea of how to go about setting it
up.

I did use the "Crows" dim as long, but just for the sake
of discussion, would it work declaring that variable as an
integer? I did it your way anyway, but I was trying to
understand all I can right now about the reason for
the "AS Long" part of that declaration.

I had already found a way to do a formula before I saw
your post, and I'm sure your way is the best by defining
the formula first as a long string, then defining the
range B1 as a formula in R1C1 fashion which I had not
seen used before. Now that I'd seen what it does, I will
use it a lot also I'm sure. The formula method I figured
out before your post was as follows:

Range("B1").Formula = "=IF(LEN(D1)=3,+""20""&RIGHT(D1,2)
&""0""&LEFT(D1,1),IF(LEN(D1)=4,+""20""&RIGHT(D1,2) &LEFT
(D1,2)))"

Then I got to your line as follows and this line was a
really great line I had not seen before, but I must
certainly commit to memory.

Range("B1").AutoFill Destination:=Range("B1", Cells
(cRows, "B"))

After that, the only other thing I did differently was to
delete column D rather than just clear the cells. I have
data in other columns that I am scooting over to the left
and I didn't need what was in column D to upload to
my "DBF" file that I am working on setting up for right
now.

I think I'm learning quickly because I can actually see
myself gaining a better understanding with each post you
give me.

Thanks again,


Bruce

-----Original Message-----
Bruce,

This should do it for you

Dim sFormula As String
Dim cRows As Long

sFormula =
"=IF(LEN(RC[+2])=3,+""20""&RIGHT(RC[+2],2)&""0""&LEFT(RC
[+2],1)," & _
"IF(LEN(RC[+2])=4,+""20""&RIGHT(RC[+2])
&LEFT(RC[+2])))"
cRows = Cells(Rows.Count, "D").End(xlUp).Row
Range("B1").FormulaR1C1 = sFormula
Range("B1").AutoFill Destination:=Range("B1", Cells
(cRows, "B"))
Range("B1", Cells(cRows, "B")).Copy
Range("B1", Cells(cRows, "B")).PasteSpecial
Paste:=xlValues
Range("D1", Cells(cRows, "D")).ClearContents