calculation question 1

[vbpadawan]

I have a grid that i allow the user to select cells that contain numeric values to perform a calculation on. When they click a button, I bring up another form that they enter the calculation in ie: "+ 1". That tells me that they want to add 1 to all the cells they selected. I do not know how to take that calculation and perform it on the selected values. Is there some function that I can use to convert a string to a calculation? So if one of the cells contains 1.43 and the calculation is " + 1" then my string is 1.43 + 1 and I want it to compute this.
Thanks for your help.

 

[Jooky68]

u can just use a loop that will go through all the cells...using the cells(row,column).value property...and than just add whatever the variable is.

for i =1 to num_rows
cells(i,2).value = cells(i,2).value +1
next i

i think that is what you were asking

 

[Jooky68]

on the above i auto. assumed you were using an excel embedded object or an excel sheet.....which i prob shouldnt of assumed...

 

[AndyGroom]

Hi,

I interpret the question as being: If I have a string that says "1+2+3" how do I get the answer 6? If so, include me in the loop cos I'd like to know how it's done too!

- Andy
_______________________________
"On a clear disk you can seek forever"

http://www.sexydesktop.co.uk

 

[vbpadawan]

No, I'm not using excell. I am using a grid control. I know how to loop throgh the selected cells but the calculation that the user enters could use any operation (+, -, *, /). They actually enter the operation and a number ie: "+ 3" or "-.05". I need to be able to take what they entered and execute the calculation with the value in the cell. If the cell value is 1.75 and the user enters - 1, I can put 1.75 - 1 in a string but I don't know how to perform the calculation on the string. Hope this makes sense.

 

[vbpadawan]

Ok, I just found another thread that gave me the answer. AndyGroom, here is the solution:

Add a reference to the Script Control 1.0

Dim objSc As ScriptControl
Set objSc = New ScriptControl
objSc.Language = "VBScript"

'Evaluate the expression
Dim dblResult As Double
strCalc = ***Build your calculation string***
dblResult = CDbl(objSc.Eval(strCalc))

Debug.Print dblResult

Let me know if you have problems, AndyGroom. I got it working.
Thanks,

Holly

 

[Jooky68]

what are the cells contained in? is it only going to be one value at a time your going to be doing the math? and also is the operation always the first character..for example is it always +1,-3,*5 etc...?? If so you can just use a simple mid function, the first character being the operation and the second character being the value that you perform the operation with again the cell value

 

[robctech]

Adding to padawan I did is as follows and works fine:

    Dim test As New ScriptControl
    test.Language = "VBScript"
    Dim a$, b$, c$
    a = "1"
    b = "+ 4"
    MsgBox test.eval(CVar(a & b))

Regards.

 

[Jooky68]

yup that will def work...

 

[AndyGroom]

Holly,

Thanks, that's really useful to know.

Have a star on me

- Andy
_______________________________
"On a clear disk you can seek forever"

http://www.sexydesktop.co.uk