locked
VBA Fix - Copy Formulas instead of Values RRS feed

  • Question

  • Hello all

    We have this code on a sheet:

    Sub Worksheet_Calculate()
    Application.EnableEvents = False
        On Error Resume Next
        Dim validCond As Boolean
        Dim rowMod As Integer
        Dim action As String
        For rowMod = ROW_FIRST To ROW_LAST
            validCond = Cells(rowMod, Columns(COLUMN_COND_STATEMENT).column).value
            action = Cells(rowMod, Columns(COLUMN_COND_ADDMOD).column).value
    
            ' modify order?
            If action = "MOD" Then
                ' if the order is filled, clear the conditional order
                Dim numFilled As Integer, numRemaining As Integer
                numFilled = Cells(rowMod, Columns(COLUMN_FILLED).column)
                numRemaining = Cells(rowMod, Columns(COLUMN_REMAINING).column)
                If numFilled <> 0 And numRemaining = 0 Then
                    Range(Cells(rowMod, Columns(COLUMN_COND_STATEMENT).column), Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column)).ClearContents
                Else
                    'if condition is met...
                    If validCond Then
                        ' copy the conditional order to the real order
                        Cells(rowMod, Columns(COLUMN_ACTION).column).value = Cells(rowMod, Columns(COLUMN_COND_ACTION).column).value
                        Cells(rowMod, Columns(COLUMN_TOTALQTY).column).value = Cells(rowMod, Columns(COLUMN_COND_TOTALQTY).column).value
                        Cells(rowMod, Columns(COLUMN_ORDERTYPE).column).value = Cells(rowMod, Columns(COLUMN_COND_ORDERTYPE).column).value
                        Cells(rowMod, Columns(COLUMN_LMTPRICE).column).value = Cells(rowMod, Columns(COLUMN_COND_LMTPRICE).column).value
                        Cells(rowMod, Columns(COLUMN_AUXPRICE).column).value = Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column).value
                        ' clear the conditional order
                        Range(Cells(rowMod, Columns(COLUMN_COND_STATEMENT).column), Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column)).ClearContents
                        ' place the order
                        Cells(rowMod, Columns(COLUMN_SYMBOL).column).Activate
                        Call PlaceModifyOrder_Click
                    End If
                End If
            ' add order?
            ElseIf action = "ADD" Then
                'if condition is met...
                If validCond Then
                    ' copy the conditional order to the real order
                    Cells(rowMod, Columns(COLUMN_ACTION).column).value = Cells(rowMod, Columns(COLUMN_COND_ACTION).column).value
                    Cells(rowMod, Columns(COLUMN_TOTALQTY).column).value = Cells(rowMod, Columns(COLUMN_COND_TOTALQTY).column).value
                    Cells(rowMod, Columns(COLUMN_ORDERTYPE).column).value = Cells(rowMod, Columns(COLUMN_COND_ORDERTYPE).column).value
                    Cells(rowMod, Columns(COLUMN_LMTPRICE).column).value = Cells(rowMod, Columns(COLUMN_COND_LMTPRICE).column).value
                    Cells(rowMod, Columns(COLUMN_AUXPRICE).column).value = Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column).value
                    ' clear the conditional order
                    Range(Cells(rowMod, Columns(COLUMN_COND_STATEMENT).column), Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column)).ClearContents
                    ' place the order
                    Cells(rowMod, Columns(COLUMN_SYMBOL).column).Activate
                    Call PlaceModifyOrder_Click
                End If
            ElseIf action <> "" Then
                MsgBox ("Invalid value " & action & " in ADD/MOD column")
            End If
        Next rowMod
    Application.EnableEvents = True
    End Sub
    



    The question is about the lines below "copy the conditional order to the real order":

                      
                        Cells(rowMod, Columns(COLUMN_ACTION).column).value = Cells(rowMod, Columns(COLUMN_COND_ACTION).column).value
                        Cells(rowMod, Columns(COLUMN_TOTALQTY).column).value = Cells(rowMod, Columns(COLUMN_COND_TOTALQTY).column).value
                        Cells(rowMod, Columns(COLUMN_ORDERTYPE).column).value = Cells(rowMod, Columns(COLUMN_COND_ORDERTYPE).column).value
                        Cells(rowMod, Columns(COLUMN_LMTPRICE).column).value = Cells(rowMod, Columns(COLUMN_COND_LMTPRICE).column).value
                        Cells(rowMod, Columns(COLUMN_AUXPRICE).column).value = Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column).value


    How can we replace those lines so that it also copies the formulas instead of just the values?

    Friday, December 30, 2016 2:17 PM

Answers

  • Ever play with the .Formula or .FormulaR1C1 properties of a range?
    e.g. Cells(5,1).Formula = cells(5,8).formula

    A .PasteSpecial would also work and you don't have to make column adjustments to the formula itself:

        Cells(5,1).Copy
        cells(5,8).PasteSpecial xlPasteFormulas


    -MainSleuth


    • Edited by MainSleuth Friday, December 30, 2016 2:52 PM More Info
    • Marked as answer by NicoPer Friday, December 30, 2016 9:36 PM
    Friday, December 30, 2016 2:52 PM