+ Reply to Thread
Results 1 to 3 of 3

Changing code from Static to Dynamic

Hybrid View

JamesFletcher Changing code from Static to... 05-29-2013, 03:56 AM
Andy Pope Re: Changing code from Static... 05-29-2013, 04:25 AM
JamesFletcher Re: Changing code from Static... 05-29-2013, 04:33 AM
  1. #1
    Registered User
    Join Date
    04-30-2013
    Location
    Cranfield
    MS-Off Ver
    Excel 2007
    Posts
    68

    Changing code from Static to Dynamic

    Hi All,

    I have some code that automatically enters a formula into a cell when a new row is created and it works fine as long as the new row is always the same; 32 in this case.
    ActiveCell.Formula = "=IF(M32-M$2<0,""Late"",""Open"")"
    M2 is today's date and M32 is the date a document is due.
    What I need to do is get the formula to look on the row that it is on...the column will always be 'M'.
    It will be part of the following code where it says "Open"
    Sub New_Document()
    '
    ' New Document Macro
    Application.ScreenUpdating = False
        Dim rowPosition As Integer
    With Worksheets("MASTER")
            rowPosition = .Cells(1, 4).Value
            .Rows(rowPosition).EntireRow.Select
                Selection.Insert Shift:=x1Down, CopyOrigin:=xlFormatFromRightOrBelow
                Rows(rowPosition + 1).Select
                Selection.AutoFill Destination:=.Range(.Cells(rowPosition, 1), .Cells(rowPosition + 1, 1)).EntireRow, Type:=xlFillDefault
                .Range(.Cells(rowPosition, 2), .Cells(rowPosition, 11)).ClearContents
                .Cells(rowPosition, 11).Select
                ActiveCell.FormulaR1C1 = "Open"
            .Cells(rowPosition, 2).Select
        End With
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,482

    Re: Changing code from Static to Dynamic

    One way

    ActiveCell.Formula = "=IF(M" & ActiveCell.Row & "-M$2<0,""Late"",""Open"")"
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    04-30-2013
    Location
    Cranfield
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Changing code from Static to Dynamic

    Genius...thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1