• Formula into a cell (Excel 2000)

    Author
    Topic
    #386229

    I need to write code which will place the following If statement as a formula into cell Q while the cursor is at cell A,

    =if(p1=””,O1,P1)

    My current code reads:

    strformula = “=If(” & CStr(ActiveCell.Offset(0.15).Address) & “=””,” & CStr(ActiveCell.Offset(0, 14).Address) & “,” & CStr(ActiveCell.Offset(0.15).Address) & “)”
    ActiveCell.Offset(0, 16).Value = strformula

    Unfortunately this does not work. Can anybody asist.

    Viewing 2 reply threads
    Author
    Replies
    • #669314

      Does the followinhg work for you ?

      Sub EnterFormula()
      Dim strFormual As String
      Dim strRow As String
      strRow = ActiveCell.Row
      strFormula = “=IF(P” & strRow & “=””””,O” & strRow & “,P” & strRow & “)”
      ActiveCell.Offset(0, 16) = strFormula
      End Sub

      If the active cell is in Column A, the formula is placed in Column Q of the same row.

      Andrew C.

    • #669322

      You have periods instead of commas and you need to use formula. Try this:

      strformula = “=If(” & CStr(ActiveCell.Offset(0, 15).Address) & “=””””,” & CStr(ActiveCell.Offset(0, 14).Address) & “,” & CStr(ActiveCell.Offset(0, 15).Address) & “)”
      ActiveCell.Offset(0, 16).Formula = strformula

    • #669315

      In the first place, to include quotes within a string, you must double them to prevent them from being seen as the end of the string. So, to include an empty string “”, you need four of them: “”””.

      Your code will create a formula with absolute references. To get relative references, use Address(False, False).

      Here is the revised code

      strFormula = "=IF(" & CStr(ActiveCell.Offset(0, 15).Address(False, False)) & _
          "=""""," & CStr(ActiveCell.Offset(0, 14).Address(False, False)) & "," & _
          CStr(ActiveCell.Offset(0, 15).Address(False, False)) & ")"
      ActiveCell.Offset(0, 16).Formula = strFormula
    Viewing 2 reply threads
    Reply To: Formula into a cell (Excel 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: