+ Reply to Thread
Results 1 to 3 of 3

vba to insert formula and auto fill based on specific column A

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,040

    vba to insert formula and auto fill based on specific column A

    I have calculation sheet from column C to F at rows 2 there are formulas .

    Trying how to use vba to insert the formula automatically still to last row based on A .
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,749

    Re: vba to insert formula and auto fill based on specific column A

    Select Developer then Start Macro Recorder

    Select Each Formula cell in turn, click inside the formula bar and press enter

    Stop the Macro Recorder.

    You will have a macro like:

    
    Sub Macro6()
    '
    ' Macro6 Macro
    '
    
    '
        Range("C2").Select
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE(""*""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(RC2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS(C2:C[-1]),50),IF(ISNUMBER(SEARCH(""cl"",RC2)),RC[-1]/100,RC[-1]))),"""")"
        Range("D2").Select
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE(""*""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(RC2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS(C2:C[-1]),50),IF(ISNUMBER(SEARCH(""cl"",RC2)),RC[-1]/100,RC[-1]))),"""")"
        Range("E2").Select
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE(""*""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(RC2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS(C2:C[-1]),50),IF(ISNUMBER(SEARCH(""cl"",RC2)),RC[-1]/100,RC[-1]))),"""")"
        Range("F2").Select
        ActiveCell.FormulaR1C1 = _
            "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE(""*""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(RC2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS(C2:C[-1]),50),IF(ISNUMBER(SEARCH(""cl"",RC2)),RC[-1]/100,RC[-1]))),"""")"
    End Sub


    Below is my list of Standard formulae.

    Save it somewhere.


    
    Range("C2:D14").Copy Range("F2")
    Workbooks("Book1.xlsx").Sheets("sheet1").Range("A1") = Workbooks("Book3.xlsm").Sheets("sheet1").Range("A1").Value*
    Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Range("A1")
    
    MyName = ActiveWorkbook.Name
    MyPath = ActiveWorkbook.Path
    MyCompletePath = ActiveWorkbook.FullName
    
    LR = Cells(rows.count,1).End(xlUp).Row
    LC = Cells(1,columns.count).End(xlToLeft).Column
    LCell = Selection.SpecialCells(xlCellTypeLastCell).Address
    LCC = Selection.SpecialCells(xlCellTypeLastCell).Column
    LCR = Selection.SpecialCells(xlCellTypeLastCell).Row
    
    Set R = Intersect(Range("A:A"), Activesheet.UsedRange)
    
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
    ActiveSheet.DisplayPageBreaks = True
    
    InStr([Start], parent_string, substring, [compare])
    
    MATCH( value, array, [match_type] )
    
    z= Columns(1).Find(y, LookIn:=xlValues, Lookat:=xlPart).row
    
    If Target.Address = "$1:$" & Rows.Count Then Exit Sub
    If Not Intersect(Target, Range("A2:A100")) Is Nothing Then"
    
    Set T = Range("A" & R).CurrentRegion
    With T
    SR = .Row
    SC = .Column
    LR = .Rows(UBound(.Value)).Row
    LC = .Columns(UBound(.Value, 2)).Column
    End With

    From my list of standard Formulae use LR ie Last Row

    LR = Cells(rows.count,1).End(xlUp).Row
    Using that line we can easily modify the recorder macro to give:=

    
    Sub Macro6()
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    
        Range("C2:C" & LR).FormulaR1C1 = _
            "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE(""*""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(RC2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS(C2:C[-1]),50),IF(ISNUMBER(SEARCH(""cl"",RC2)),RC[-1]/100,RC[-1]))),"""")"
        Range("D2:D" & LR).FormulaR1C1 = _
            "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE(""*""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(RC2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS(C2:C[-1]),50),IF(ISNUMBER(SEARCH(""cl"",RC2)),RC[-1]/100,RC[-1]))),"""")"
        Range("E2:E" & LR).FormulaR1C1 = _
            "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE(""*""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(RC2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS(C2:C[-1]),50),IF(ISNUMBER(SEARCH(""cl"",RC2)),RC[-1]/100,RC[-1]))),"""")"
        Range("F2:F" & LR).FormulaR1C1 = _
            "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE(""*""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(RC2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS(C2:C[-1]),50),IF(ISNUMBER(SEARCH(""cl"",RC2)),RC[-1]/100,RC[-1]))),"""")"
    End Sub
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: vba to insert formula and auto fill based on specific column A

    Sub twist()
    
    
    Range("C2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE("" * ""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($B2),""litres"",""""),""litre"",""""),""cl"",""""),"","","".""),""L"",""""),""*"",REPT("" "",50)),50*COLUMNS($B:B),50),IF(ISNUMBER(SEARCH(""cl"",$B2)),B2/100,B2))),"""")"
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Insert rows based on cell value then auto fill blanks
    By warrenr6 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2017, 05:50 PM
  2. How to disable auto fill formula from table but only specific column
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2017, 07:38 AM
  3. Dynamic Auto-Fill Range based upon Specific number of rows
    By a333 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2013, 03:54 AM
  4. auto fill time based on a when a specific cell has data entered
    By b16dlg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-30-2012, 05:26 AM
  5. Replies: 0
    Last Post: 08-10-2012, 12:55 PM
  6. Replies: 1
    Last Post: 09-07-2011, 05:57 PM
  7. [SOLVED] Auto Fill Column with Date based on rows in other column
    By JOUIOUI in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2006, 01:35 PM

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