Calculation using Macro ( MS Excel 2007 )

[hally]

Good day..

I want to do simple calculation using Macro in MS Excel 2007..

All the data is in Sheet1 ( Which is 13a ), the calculation is in Sheet2 ( Which is work ) and the result of the calculation will come out in Sheet3 ( Which is result ).. Here is my coding..

Sub cmdOK_Click()
Dim xl_result, xl_13a, xl_work As Worksheet
Dim oper
Dim valueO


'Dim cellW As Range

'sheet13a = Sheets("work").Range(F6).value
'sheetR = Sheets("work").Range(G6).value
Set xl_work = Sheets("work")
Set xl_13a = Sheets("13a")
'xl_work.Select

'oper = xl_work.Range("C6").value
'valueO = xl_work.Range("D6").value
valueO = 2
'
'a = 5
'i = 5
'strjcn = "'"
'rw = 2
'CNT = 1
'Status = ""

   

'xl_result.Select
xl_result.Range("E6") = xl_13a.Range("E6").value + valueO

End Sub

Here is my attachment.. Maybe some of you can help me..


Thank you so much for your kind help..

[shg]

Option Explicit

Sub cmdOK_Click()
    Dim wksResult As Worksheet
    Dim wks13a    As Worksheet
    Dim valueO    As Double

    Set wks13a = Worksheets("13a")
    Set wksResult = Worksheets("result")

    valueO = 2
    wksResult.Range("E6").Value = wks13a.Range("E6").Value + valueO
End Sub

[hally]

Thank you shg..

But it still didn't work.. how about for example the user want to do the math operation beside addition.. maybe my coding is wrong somewhere..

[shg]

hally, your code is wrong; my code is a correction of your code. I don't know what you want to do.

[Paul]

Hi Hally, try this code instead:

Public Sub myMacro()
Dim oper As String
Dim oValue As Long
Dim oCell As String
Dim sht1 As Worksheet, sht2 As Worksheet

With Sheets("work")
    Set sht1 = Sheets(.Range("F6").Text)
    Set sht2 = Sheets(.Range("G6").Text)
    oCell = .Range("E6").Text
    oper = .Range("C6").Text
    oValue = .Range("D6").Value
End With

Select Case oper
    Case Is = "+"
        sht2.Range(oCell).Value = sht1.Range(oCell).Value + oValue
    Case Is = "*"
        sht2.Range(oCell).Value = sht1.Range(oCell).Value * oValue
    Case Is = "/"
        sht2.Range(oCell).Value = sht1.Range(oCell).Value / oValue
    Case Is = "-"
        sht2.Range(oCell).Value = sht1.Range(oCell).Value - oValue
    Case Else
        MsgBox "Operator must be +, -, * or /"
End Select
End Sub

After pasting this code into your module, remember to right-click on your command button, select 'Assign Macro' and choose 'myMacro' from the list of available macros. Set your values in cells C6:G6 on sheet 'work' and click the button. The sheet listed in G6 will be updated accordingly.

[hally]

Thank you both of you..

pjoaquin,


How to:

Set your values in cells C6:G6 on sheet 'work' and click the button. The sheet listed in G6 will be updated accordingly.

I still don't know and still get error..

Thank you so much for help..