Newbie in deep..... Excel and VB

  • Thread starter Thread starter BEVERNON
  • Start date Start date
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!
 
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
 
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
 
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
 
If you are having a problem with spaces being left in or added simple use the TRIM function (can be used in VBA and Excel) to remove excess spaces.
 
I'm in the same position...I recommend "Excel 2002 Power Programming with VBA" by John Walkenbach, ISBN 0764547992.
Toby Erkson
Oregon, USA
 
Thanks to those of you who pointed out the space.... I used a trim function and
it works perfectly. Thanks again!
Brad
 
Back
Top