excel macro to applescript help

Discussion in 'Mac Basics and Help' started by mescalin3, Apr 29, 2010.

  1. mescalin3 macrumors newbie

    Joined:
    Apr 29, 2010
    #1
    I have this worksheet created in Excel 2007 in which I wrote a macro to help me enter data into a table. I need to have the same funcionality in Office 2008 but after doing some reading, I still haven't figured out how to do it.

    I'll describe how my macro works:
    [​IMG]
    a- the first combo box let's me choose whether I want to add or subtract a quantity.
    b- the second one, let's me choose the type of product that i want to modify the quantity.
    c- the underlined cell is where I input the quantity i want to add/subtract
    d- when I click the button, all the macro does is that depending on the type selected on the second combo box, copies the new value (the selected cell in the pic) and pastes it in the corresponding cell for that type of product. in this case it would paste 22,379 in cell F10 because type "h" is selected.
    e- then it puts a zero in the cell mentioned in C.

    it's rather simple and the only macro I believe but I don't know how to write it in applescript.

    I'll include my macro code:
    Code:
    Sub modificar()
    
    If Range("D26") > "0" Then
        If Range("D56") = "1" Then
            Range("D26").Select
            Selection.Copy
            Range("F3").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "2" Then
            Range("D26").Select
            Selection.Copy
            Range("F4").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "3" Then
            Range("D26").Select
            Selection.Copy
            Range("F5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "4" Then
            Range("D26").Select
            Selection.Copy
            Range("F6").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "5" Then
            Range("D26").Select
            Selection.Copy
            Range("F7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "6" Then
            Range("D26").Select
            Selection.Copy
            Range("F8").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "7" Then
            Range("D26").Select
            Selection.Copy
            Range("F9").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "8" Then
            Range("D26").Select
            Selection.Copy
            Range("F10").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "9" Then
            Range("D26").Select
            Selection.Copy
            Range("F11").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "10" Then
            Range("D26").Select
            Selection.Copy
            Range("F12").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "11" Then
            Range("D26").Select
            Selection.Copy
            Range("F13").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "12" Then
            Range("D26").Select
            Selection.Copy
            Range("F14").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        If Range("D56") = "13" Then
            Range("D26").Select
            Selection.Copy
            Range("F15").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        End If
        End If
        Range("D24").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=0"
    End Sub
    That first if (D26>0) tells the macro that there is an actual value to add/subtract, if it's greater than zero it proceeds if not it does nothing.
    There is an if for each type of product, as you can see. cell D56 is where the product number is located, so if that cell is 1, for example, then product type is 1 so the first quantity value from the table is selected (F3). If D56 is two, then product type is two, so the following cell (F4) is selected and the modified value from cell D26 is pasted there. So on and so forth for all types. Then, that last bit, changes the value in D24 to zero.

    I realize the code could be much better and i will get to that but I'd like to see what that would look like in applescript first.

    any help would be appreciated greatly. :)
     
  2. complexcommunic macrumors regular

    Joined:
    Feb 23, 2010
    #3
    We don't need to see all your code. Office 2008 for mac has very minimal macro support. You could try open office for mac, it works a bit better.
    Otherwise I don't know what to tell you... Try office 2004, I think it worked better. Otherwise wait for office 2011, it'll be here pretty soon I think.
     

Share This Page