Ok, I've found a new thing that I could do inside QPW
macros that I don't know how to do inside of Excel VBA.

I am open to accomplishing this however is necessary in
Excel. But I just wanted to describe what actually needs
to be taking place. In summary I am putting in a formula
while in Cell B1 (see below) that is based on data in Cell
D1, and copying that formula down to however many rows in
Column B are necessary to match up with what is in column
D. Then once I copy the formula far enough down in column
B, I need to paste the values that resulted from that
formula in those rows in column B. Then after that the
column D will no longer be necessary, and it will be
deleted.

My formula that I want to put in and then copy is as shown
below:


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

Once I learn this technique here, then I should be off to
the races again for a while I hope.


Bruce

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

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