Enter Formula in a cell with VBA

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

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
 
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.
Click to expand...

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)))"
Click to expand...

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"))
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
Back
Top