Insert formulas to new lines in table

L

Ltuk85

New Member
Joined
Sep 26, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi - I have a table that gets updated with new data in columns D-F utilising a flow.
I then need to populate formulas in columns G-N from the first blank cell in column G to the last row of the table (based on data in column D).
The formulas are look ups to another sheet that is downloaded from an external system and copied and pasted into sheet 2 named MCAP Supplier Console. This data changes so old values disappear as such I can't just apply formulas to all columns as that will cause older records to pull through as N/A and so I am currently copying all data and pasting as values after using the formulas to pull the new data through.

I have tried a number of ways to populate cells with the formulas but after hours of searching for help I am really struggling to find how to select the first blank entry in column G and populate the formulas down to the last table entry.
At the moment I have been doing this manually by just hovering over bottom right of cell and double clicking to fill down but I would like this to be a step undertaken by the macro so anyone in my team could complete the task by just pressing one command button.

My code is currently this which works to populate the formula but only in a set range so any help on how to amend the code to set the range to the blank columns in the new rows only.

Sub Test2()

Dim strFormulas(1 To 8) As Variant

With ThisWorkbook.Sheets("POs Received")
strFormulas(1) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),12)"
strFormulas(2) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),5)"
strFormulas(3) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),6)"
strFormulas(4) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),1)"
strFormulas(5) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),16)"
strFormulas(6) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),13)"
strFormulas(7) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),8)"
strFormulas(8) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),9)"

.Range("g5644:n5644").Formula = strFormulas
.Range("g5644:n5660").FillDown
End With
End Sub
 

Attachments

  • PO Sheet.png
    PO Sheet.png
    22.4 KB · Views: 6

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
See if this does what you are after.
(Change the table name to your table name)

Rich (BB code): 
Sub GetBlankRangeForFormulas()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rowLastPO As Long, rowLastVal As Long, colVal As Long
    Dim rngFormula As Range
    
    Dim strFormulas(1 To 8) As String
    
    Set ws = ThisWorkbook.Sheets("POs Received")
    Set tbl = Range("Table1").ListObject                    ' <--- Change this to your table name
    
    With tbl
        rowLastPO = .ListColumns("PO Number").Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        rowLastVal = .ListColumns("PO Value").Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        colVal = .ListColumns("PO Value").Range.Column
    End With
    
    strFormulas(1) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),12)"
    strFormulas(2) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),5)"
    strFormulas(3) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),6)"
    strFormulas(4) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),1)"
    strFormulas(5) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),16)"
    strFormulas(6) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),13)"
    strFormulas(7) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),8)"
    strFormulas(8) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),9)"
    
    Set rngFormula = ws.Cells(rowLastVal + 1, colVal).Resize(, 8)
    rngFormula.Formula = strFormulas
    rngFormula.Resize(rowLastPO - rowLastVal).FillDown
    
End Sub
 
Upvote 0
Solution
Alex Blakenburg said:
See if this does what you are after.
(Change the table name to your table name)

Rich (BB code): 
Sub GetBlankRangeForFormulas()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rowLastPO As Long, rowLastVal As Long, colVal As Long
    Dim rngFormula As Range
   
    Dim strFormulas(1 To 8) As String
   
    Set ws = ThisWorkbook.Sheets("POs Received")
    Set tbl = Range("Table1").ListObject                    ' <--- Change this to your table name
   
    With tbl
        rowLastPO = .ListColumns("PO Number").Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        rowLastVal = .ListColumns("PO Value").Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        colVal = .ListColumns("PO Value").Range.Column
    End With
   
    strFormulas(1) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),12)"
    strFormulas(2) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),5)"
    strFormulas(3) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),6)"
    strFormulas(4) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),1)"
    strFormulas(5) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),16)"
    strFormulas(6) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),13)"
    strFormulas(7) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),8)"
    strFormulas(8) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),9)"
   
    Set rngFormula = ws.Cells(rowLastVal + 1, colVal).Resize(, 8)
    rngFormula.Formula = strFormulas
    rngFormula.Resize(rowLastPO - rowLastVal).FillDown
   
End Sub
Click to expand...
That looks like it's worked perfectly
Did seem to stop table auto-calculating for no obvious reason and logical solutions not fixing it so added find and replace = with = code line to end as a workaround
 
Upvote 0
Alex Blakenburg said:
See if this does what you are after.
(Change the table name to your table name)

Rich (BB code): 
Sub GetBlankRangeForFormulas()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rowLastPO As Long, rowLastVal As Long, colVal As Long
    Dim rngFormula As Range
   
    Dim strFormulas(1 To 8) As String
   
    Set ws = ThisWorkbook.Sheets("POs Received")
    Set tbl = Range("Table1").ListObject                    ' <--- Change this to your table name
   
    With tbl
        rowLastPO = .ListColumns("PO Number").Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        rowLastVal = .ListColumns("PO Value").Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        colVal = .ListColumns("PO Value").Range.Column
    End With
   
    strFormulas(1) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),12)"
    strFormulas(2) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),5)"
    strFormulas(3) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),6)"
    strFormulas(4) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),1)"
    strFormulas(5) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),16)"
    strFormulas(6) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),13)"
    strFormulas(7) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),8)"
    strFormulas(8) = "=INDEX('MCAP Supplier Console'!A:U,(MATCH([@[PO number]],'MCAP Supplier Console'!J:J,0)),9)"
   
    Set rngFormula = ws.Cells(rowLastVal + 1, colVal).Resize(, 8)
    rngFormula.Formula = strFormulas
    rngFormula.Resize(rowLastPO - rowLastVal).FillDown
   
End Sub
Click to expand...
Forgot to say thank you in previous post - this will save me a huge amount of time going forward and allow others to pick up work when I'm on leave
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Multiple Layer VLOOKUP, or better alternative?
Replies
9
Views
494
JukeExcel
J
J
  • Solved
Counting and Indexing?
Replies
8
Views
270
Fluff
Fluff
S
  • Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
260
STARL0RD
S
C
  • Question
Return closest date based on three criteria being true, or return "no date found"
Replies
8
Views
223
chris_bosten
C
W
  • Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
514
Micron
M

Forum statistics

Threads
1,221,831
Messages
6,162,250
Members
451,757
Latest member
iours

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top