Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula into a cell (Excel 2000)

    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.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula into a cell (Excel 2000)

    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.

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula into a cell (Excel 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula into a cell (Excel 2000)

    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<pre>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</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •