Newbie in deep..... Excel and VB

B

BEVERNON

Hi everyone.
I'm an experienced programmer, just not experienced with Excel and VB. I have
to populate a spread sheet programmatically. On a button click, I am clearing
the worksheet, and then I compute a formula that goes into each cell. It
doesn't work!

This is what I'm doing (snipped somewhat)

Dim myFormula as string

....

myFormula = "=A1+B1"
cells(10, 10).formula = myFormula

..... that works.

So does.... cells(10,10).formula = "A1+B1"

Now if I need to compute the formula at runtime like this....
Let x=1
Let y=1
myFormula = "=A"+str(x)+"+B"+str(y)
and try cells(10, 10).formula = myFormula I get an error.

In each case the value of myFormula is "=A1+B1"

Anybody ever tried dynamically computing a formula and assigning it to a cell?

I've been working on XL for the past week, and have been very impressed with
the capabilities, but before I can become a true believer I need to know how to
do this...

Similarly, how can I dynamically compute a range? I would like to pass a
computed string to a statement like this:

range(mycomputedstring).formula = myFormula

Thanks in advance for your help.

Brad Vernon
Help!
 
C

Chip Pearson

Instead of using the Str function, use CStr to convert your variables x and
y to strings. Str puts a space in front of the string.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
V

Vic Eldridge

myFormula = "=A"+str(x)+"+B"+str(y)

Str() returns a string with a leading space if the number is positive.
Those leading spaces are screwin up the syntax of the Formula
property.
Try CStr() instead.


range(mycomputedstring).formula = myFormula
Click to expand...

This should work fine. Just make sure mycomputedstring contains a
valid address enclosed in quotes. It could also contain the name of a
named range instead of
an address.


Regards,
Vic Eldridge
 
R

ross

Hi Brad

basicly this will do what you ask - i think!

Private Sub CommandButton1_Click()

Dim strFor As Variant
x = 1
y = 2

strFor = "=A" & x & "+b" & y

Sheets("sheet1").Cells(3, 1).Value = strFor

End Sub

You see that it's the same as your code, just with diffrent ways of
link the bite up! (the &)
However, i have ever done any thing like this, but i would wayger that
there is a better way to do it... row and colum formuals spring to
mind, but hey this get the job done.

NB. the cells(3,1) refers to row 3, col A, and so on,
tools>options>genral>R1C1 stlye

As for ranges

you can use somehting like

Private Sub CommandButton1_Click()

Dim MyRange As Range



With ActiveSheet
.Range(.Cells(2, 4), .Cells(4, 5)).Name = "tevor"
End With


Set MyRange = ActiveSheet.Range("tevor")

End Sub

agian there are lost of ways to define ranges and there are lots of
things you can do to change ranges,

any way hope this helps,
rosscoe
 
T

Toby Erkson

I'm in the same position...I recommend "Excel 2002 Power Programming with VBA" by John Walkenbach, ISBN 0764547992.
Toby Erkson
Oregon, USA
 
B

BEVERNON

Thanks to those of you who pointed out the space.... I used a trim function and
it works perfectly. Thanks again!
Brad
 
Top