how to use VBA values in "formula=" expressions?

G

Guest

Can anybody just give a clue how to transfere information from VBA to a
formula? Let's say:

If I in a Sub have
A = 4
B = 8

How can I use that figures in a formula in let's say cell A1?
Application.Range("A1").Formula = "=4+8"

I tried .Formula= "=A+B" but of course, it doesn't work since it's an
compleatly other "environment".

One way is to use .Value = A in one cell, .Value = B in an other and let the
formula in the third cell to point to that pair of cells and the hide the
other two, but that isn't god enought.

How should I do?


/regards
 
A

AlfD

Hi!

I'm not entirely sure what you are trying to do or why you are usin
.formula. So: this might hit the button or miss the point completely.

Are A and B the results of calculations in your sub?
If they are, then A+B will be their sum and can be put directl
_as_a_number_ into A1

e.g. if A1 is on sheetA insert a line in your sub
worksheets("SheetA").Range("A1")=A+B

So if A=4 and B=8, A1 will now read 12.

Al
 
D

Don Guillett

Actually, this can be shortened to
Range("a1").Formula = "=" & a & "+" & b
or just the value by
range("a1").value=a+b
 
G

Guest

Thank you Don and AlfD,

I believe Don vocered my need with Range("a1").Formula = "=" & a & "+" & b
I belive there will be a formula in the cell with that.

I like to have a formula the users can actually see and follw the logic in
the cells and therefore the .Value= approach is second chioce. I'll try and
see.

/Regards once more !
 
G

Guest

Hi again,
I have a follow up question to get it right. Debugger complain about
"incompatible types"
I need to copy a lot of cells from one sheet to an other. The cells in
sheet1.range1temp are text (they are actually formulas without an "=" in the
beginning, formated as "text") and I want all the cells in sheet2.range1 to
become "their formulas"

Dim X As Variant
Dim rng1temp As Range
Dim rng1 As Range

Set rng1 = Sheet1.Range("C29:G48")
Set rng1temp = Sheet2.Range("C29:G48")

X = Sheet1.rng1temp.Formula
Sheet2.Activate
ActiveSheet.rng1.Formula = "=" & X ' <---------"Incompatible types"
error message

Whats wrong?

/Regards
 
G

Guest

It's in the line
X = Sheet1.rng1temp.Formula
"Incompatible types" occurs, not
ActiveSheet.rng1.Formula = "=" & X

/Regards
 
Top