how to write a formula

[jkpd2000]

Hi

I want something like this

amount = b1 + b2

simple but i want it in vba script
and b1 and b2 are in variables.

example.

qty = "b1"
rate = "b2"

and a1 = qty * rate

where amount represents cell a1 and qty cell b1 and rate cell b2

if i have b1 = 5 and b2 = 4

a1 should result in 20

all this should be through vba.

[Ivan F Moala]

On 2002-05-20 08:25, jkpd2000 wrote:
Hi

I want something like this

amount = b1 + b2

simple but i want it in vba script
and b1 and b2 are in variables.

example.

qty = "b1"
rate = "b2"

and a1 = qty * rate

where amount represents cell a1 and qty cell b1 and rate cell b2

if i have b1 = 5 and b2 = 4

a1 should result in 20

all this should be through vba.

Sub example()

Dim Qty As Double

Dim Rate As Double

Dim Result As Double



Qty = [B1]

Rate = [B2]

Result = Qty * Rate

[A1] = Result



End Sub

[tealeaf]

i'm sure there is a better way to do it which i'm very interested in reading about. but the way i normally do it is
say u want a1 = b1 * c1 then i do
Range("a1").Value = Range("b1").Value * Range("c1").Value
is that what u meant?
good luck

[Iain Lewis]

Try defining the ranges first :

MyValue1 = Range("B1")
MyValue2 = Range("B2")
MyAmount = MyValue1 + MyValue2

You may have to define the worksheet which B1 & B2 are on eg Sheets("sheet1").Range...

Let me know if this isn't what you want.

Iain

[tealeaf]

wow. so does that mean that [B1] is equivalent to the Range("B1").Value or do they have subtle differences?
thanks

[jkpd2000]

i have b1 and b2 are in some variables.

example

rate = "b1"
qty = "b2"

and b1 contains 5
and b2 contains 4

i want

a1 = 20

[Ivan F Moala]

On 2002-05-20 08:45, jkpd2000 wrote:
i have b1 and b2 are in some variables.

example

rate = "b1"
qty = "b2"

and b1 contains 5
and b2 contains 4

i want

a1 = 20

Sorry don't understand....I think all of the
above have accomplished what you want ?
Could you please clarify more ?

[jkpd2000]

I have two variable

rate and qty

which have the cell names

rate = "b1" and qty = "b2"

i want a1 cell should result in the b1*b2

which b1 = 5 and b2 = 4

and i should get a1 = 20

P.S. b1 and b2 might change for every run.

that is the reason they are in variables.

[Ivan F Moala]

On 2002-05-20 08:39, tealeaf wrote:
wow. so does that mean that [B1] is equivalent to the Range("B1").Value or do they have subtle differences?
thanks

This syntax to be a shortcut way of referencing objects.So using [] instead of parenthesis means you don't need to type all the quotes and you don't need to state what kind of object you're referring to. Also Using square brackets (for example, "[A1:C5]") is identical to calling the Evaluate method with a string argument.
It may save time and simplify code and in some cases simple means faster, but it may also mean more obscure.

[jkpd2000]

Qt = "a1"
Rt = "b2"

Range("a1").Value = Range(qt).Value * Range(Rt).Value

this is working...but i want something like this...

qty = "a" & "1"

how to join two string which make a cell name?


[ This Message was edited by: jkpd2000 on 2002-05-20 12:55 ]