Forum Discussion

NLepley's avatar
NLepley
Copper Contributor
Dec 06, 2022

Macro formula help

I am trying to create a macro to populate future dates once a location and date are entered into a row. I enter the address and dig date then framing is +15 days from dig date, RI is +35 days from dig date and house closing is +110 days after dig date. I currently just have =B2+15 for framing and then drag it down for new addresses. I didnt know if this could be a macro so if the days after dig date changes I can update it easier than changing all the formulas.

AddressDig DateFramingRIHouse Close
123412/6/202212/21/20221/10/20233/26/2023

 

Thank you for any help!

Macros and VBA

8 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Dec 06, 2022

    NLepley 

    If you're using 365 and have access to LAMBDA, this might be an option for you:

     

    A Lambda named 'Dig'.  This function creates a dynamic range based on the dates you enter in the dig date column.  You simply provide it with the day intervals (e.g. 15, 35, 110)

    =LAMBDA(Framing,RI,House_Close,LET(
    dates, Sheet1!$E$2:$E$1000,
    nonblanks, COUNTA(dates),
    dig, TAKE(dates, nonblanks),
    dig + HSTACK(Framing, RI, House_Close)
))

    At the sheet level, the formula is simple:

    =Dig(15,35,110)

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 06, 2022

    NLepley 

    I'd still use a formula. Store days after dig for Framing in a cell, say in Z1. You can then use

    =B2+$Z$1.

    Similarly, store days after dig for RI in another cell, say Z2, and use =B2+$Z$2

    And so on.

    • NLepley's avatar
      NLepley
      Copper Contributor
      Dec 06, 2022
      I didn't know if I could avoid having to drag down the formulas for new address that will be added of if a macro could be created to update them once a new address is added with the dig date.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 06, 2022

        NLepley 

        If you prefer VBA code:

        Right-click the sheet tab.

        Select 'View Code' from the context menu.

        Copy the code listed below into the worksheet module.

        Switch back to Excel.

        Save the workbook as a macro-enabled workbook (.xlsm)

        Make sure that you allow macros when you open it.

        Private Sub Worksheet_Change(ByVal Target As Range)
    Const Interval1 = 15
    Const Interval2 = 35
    Const Interval3 = 110
    Dim rng As Range
    Dim dtm As Date
    If Not Intersect(Range("B2:B" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("B2:B" & Rows.Count), Target)
            If rng.Value = "" Or Not IsDate(rng.Value) Or rng.Offset(0, -1).Value = "" Then
                rng.Offset(0, 1).Resize(1, 3).ClearContents
            Else
                dtm = rng.Value
                rng.Offset(0, 1).Resize(1, 3).Value = _
                    Array(dtm + Interval1, dtm + Interval2, dtm + Interval3)
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Resources