Hi Bruce,

Answering your points in order.

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.

Yes it certainly would, as long as you don't have more than 32768 rows of
data. I use Long instead of Integer because Windows is a 32-bit operating
system, and is more efficient with 32-bit (Long) integers. 16-bit Integers
(Integer) are converted 32-bit before any arithmetic function is performed
on them, and then converted back to 16-bit. Thus, Long is more efficient.

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)))"

I setup the formula in a string to aid debugging, it's much eaiser to debug
the vraiable than the statement setting the formula.

There is nothing wrong with your formula, you are just using A1 notation, I
use R1C1 notation. The reason I use this is that I find it easier to embed
variables in this. For instance, if you calculate the column and store it in
a variable myCol, my formula reads

sFormula = "=IF(LEN(RC" & myCol & ")=3,+""20""&RIGHT(RC" & myCol &
",2)" & _
"&""0""&LEFT(RC" & myCol & ",1)," & _
"IF(LEN(RC" & myCol & ")=4,+""20""" & _
"&RIGHT(RC" & myCol & ")&LEFT(RC" & myCol & ")))"

whereas A1 notation would read

sFormula = "=IF(LEN(" & CHR(myCol+64 & myRow")=3,+""20""&RIGHT(" &
CHR(myCol+64 & myRow",2)" & _
"&""0""&LEFT(" & CHR(myCol+64 & myRow",1)," & _
"IF(LEN(" & CHR(myCol+64 & myRow")=4,+""20""" & _
"&RIGHT(" & CHR(myCol+64 & myRow")&LEFT(" & CHR(myCol+64 &
myRow")))"

(this may not bew completely accurate, but I am sure you get the picture).


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"))

Thanks, it's useful I agree.

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.

Yes sorry, I think you originally said that, but I misinterpreted it.


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

That's what it's all about, so I am pleased. I look forward to seeing you
responding to others in the future.

Regards

Bob