Converting formula to a code

I have a simple formula of multiplication

For ex: =A1*60.

I would like to know how can i convert this simple formula into a macro coding.

Can any one help me.

Hi Saky,

Using VBA, if you wanted your above formula in A2 the code would be:

Range("A2").Formula = "= A1 * 60"

hth

Hi Marvin,

Thanks for your quick revert!!!!

If i need this macro to function when i change the cursor from one cell to the other...i meant to say if i want to do it change event how do i need to start the VBA coding.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A2").Formula = "= A1 * 60"
End sub

Is that correct???

One example

Range("A2") = Range("A1") * 60

What are you really wanting to do?

Please wrap your code in code tags, before the moderators get you...

Forum rules

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

You need to use the Worksheet SelectionChange event

An example of what you are trying to do would not go amiss.

Cheers.

Sorry for troubling you without giving the sample workbook Marcol.

I have attached a workbook.

In that if i enter values in say A3, then C3 should give me the result by multiplying the value into 60. and the same way if i enter the value in B3 then C4 sholud show me the result.

Can you help me to do this using VBA coding. i want this to happen till the range A20 to D20

Try this in the worksheet module

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim isect As Range

    If Target.Cells.Count > 1 Then Exit Sub

    On Error GoTo ResetApplication
    Application.EnableEvents = False

    Set isect = Intersect(Target, Range("A3:A20"))

    If Not isect Is Nothing Then
        If Target <> "" Then
            Range("C" & Target.Row) = Target * 60
        Else
            Range("C" & Target.Row) = ""
        End If
    End If

ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.EnableEvents = True
    Set isect = Nothing

End Sub

Hope this helps

Friend, Thanks for your great help...This code is awesome it works great!!!!

But in my original excel instead of column A,B and C,D i have the columns as Col O,P and Q,R. So i just copy and pasted the coding and made some changes as below. But it is not working in it...Can you clarify where am i going wrong...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim isect As Range

    If Target.Cells.Count > 1 Then Exit Sub

    On Error GoTo ResetApplication
    Application.EnableEvents = False

    Set isect = Intersect(Target, Range("O3:O20"))

    If Not isect Is Nothing Then
        If Target <> "" Then
            Range("Q" & Target.Row) = Target * 60
        Else
            Range("Q" & Target.Row) = ""
        End If
    End If

ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.EnableEvents = True
    Set isect = Nothing

End Sub

Seems to work for me

Try this version of your workbook

1/. I have added a sheet "Lookups" and created dynamic named lists for each catagory you require for data validation, you can add /change/ delete any data in these lists.
Don't leave blank cells in these lists, if you want to delete an item use Delete Cells > Shift Cells Up

This makes the code in the worksheet change event much simpler.

This Lookup sheet can be hidden.

2/. I have changed you macro "Test" to "TestStatus" and moved it's trigger to the ThisWorkbook module, Workbook_Open event, and also called it in the Worksheet_Change event (Sheets("Status"))

I have assumed that each project will have an SI number and the last row in this column ("A") determines the number of rows to check the status as changes are made.

3/, 2003 can only handle three conditional formats, I have changed the base colour of Column J to allow the colour codes you require

Try changing the data in Column E to test the drop-Down in F

Try changing dates in columns H K & L to se the result in column I, you probably don't need the validation in column I, but I have left it just in case.

Hope this helps

Happy to help.

You would do well to have a look as this link on Dynamic Named Lists

http://www.cpearson.com/Excel/excelF.htm#DynamicRanges

and follow the links from there