Table Formula question

StewartS

Board Regular
Joined
Feb 24, 2002
Messages
217
I have vlookup formula in a table which I have changed the table array details for to a new array. The table array was changed following an upgrade of our intranet where the original information came from.

I have amended the formula as it is in about 100 work books using a macro which opens the workbooks and then does a simple replace. This has worked OK and updated the details as required. However if a new line is inserted in the table it keeps the old table array details in the lookup. Is there a way of forcing the table to use the new array details rather than the old ones when lines are inserted.

By the way the lines are inserted using a macro as the workbooks and sheets are protected, I know I could add replace into the macro which would solve the issue but didn't really want to go down this route.

StewartS
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi StewartS,

Yes, there is a way to do this. If the formulas are coming back as something different, Excel is seeing the calculated column formula, which is written into the XML of the file automatically (see a quick write-up about it here: Remembering Table Formulas | EXCEL TABLES). To do this manually, you would need to enter the formula, and then* click the button to overwrite all cells with that formula.

* Excel will only show this button under certain conditions. Such as, if you already have any value in a cell in the column, then you enter a formula, Excel doesn't want to assume it should just overwrite everything. However, if you amend the formula which is saved as the calculated column formula, Excel will go right to work.

That being said, post your code and we'll amend it to work for you. With code I find it's best to use the Table objects instead of a regular Range object.
 
Upvote 0
Zack Thanks for the response - at this stage I must say that your book is sitting on my desk and it has been very useful :). Its just helped me redefine the way I loop through the workbooks.

The existing formula is =IFERROR(VLOOKUP([@FUNC], lovs!h_prjnew ,2,FALSE),""), at present the named range for the array is at sheet level so is lovs!h_prjnew. Which was based upon the old intranet page details.

The new Array FuncDet will be a Dynamic range using Offset and CountA to define the named range, I've done this to exclude some unneeded header details which come down from the web page. The new formula will be =IFERROR(VLOOKUP([@FUNC], FuncDet ,2,FALSE),""). This will be in a table called Detail on a sheet called Input and the formula is in a column called Function Code.

The initial code I used within a loop was just a standard replace --- Cells.Replace What:="lovs!h_prjnew", Replacement:="FuncDet", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Which is within a loop which opens each workbook, unprotects the sheet, does the replace and then reprotects the sheet then saves and closes the workbook.

The concept of the table is that a user enters a 4 character code in the Func column and the Vlookup brings back the description for that code. The details from the Intranet are updated each time the workbook is opened to ensure that the information is always up to date.

I assume that the suggested revised code will change the formula details in the underlying xml as described in your blog.

Thanks for your help.

StewartS
 
Upvote 0
First of all, thank you very much for the kind words about the book. I greatly appreciate it, and certainly hope you enjoy it! It was a lot of fun to write.

One of the benefits of Tables is having calculated columns. As I said, it’s written into the XML of the file when entered, which is how the formula will get auto-completed in new rows added. Changing the calculated column formula can be tricky though. To do this, we don’t need to do a Find/Replace, but just change the DataBodyRange.Formula, which should force Excel to re-enter the calculated column.

The key is the whole column needs to be done at the same time. Technically a Find/Replace does each cell individually, even though it’s one action. The manual way to do this would be to select the entire column (body only, no header, no footer), enter your formula, then press CTRL+ENTER to confirm for every cell at once. (Or clear all cells and confirm with just ENTER.)

As I understand it, you have some sort of loop to iterate through multiple workbooks. Below is what I would use for each workbook in the iteration. My assumption is you will be able to change the objects in the code below for the objects in your code. So the TargetBook variable is the workbook variable in the iteration. There’s a bit of redundancy and error checking in the code, like if the sheet exists, etc. You don’t have to use all that if you don’t want to, but I generally find it’s better to err on the side of caution than have your code throw an error. But it can make for much longer code than originally anticipated.

Below is a routine and three functions to assist. The code I would point you to is in the first routine. Copy/paste the other functions into your project as well. If you need help integrating this first routine into your code just let us know (and post your code as well).

Code:
 Sub StewartTableCode()

    Dim TargetBook As Workbook
    Dim TargetTable As ListObject
    Dim TargetColumn As ListColumn

    Set TargetBook = ActiveWorkbook    '<- for testing purposes only, change to your iterative workbook

    ' If you're not already, I would turn off a few application settings prior to any looping...
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    ' Make sure the sheet exists
    If WorksheetExists("Input", TargetBook) Then
        ' Make sure the Table exists
        If TableExists("Detail", TargetBook.Worksheets("Input")) Then
            ' Make sure the "Function Code" column exists
            If TableColumnExists("Function Code", TargetBook.Worksheets("Input").ListObjects("Detail")) Then
                ' Make sure the "FUNC" column exists
                If TableColumnExists("FUNC", TargetBook.Worksheets("Input").ListObjects("Detail")) Then

                    ' Set our objects and enter the function, ensure the column is clear first just in case
                    Set TargetTable = TargetBook.Worksheets("Input").ListObjects("Detail")
                    Set TargetColumn = TargetTable.ListColumns("Detail")
                    TargetColumn.DataBodyRange.ClearContents
                    TargetColumn.DataBodyRange.Formula = "=IFERROR(VLOOKUP([@FUNC],FuncDet,2,FALSE),"""")"

                End If
            End If
        End If
    End If

    ' Good to do in an iteration, just at the end before the next loop
    Set TargetBook = Nothing
    Set TargetTable = Nothing
    Set TargetColumn = Nothing

    ' Set everything back after running your code
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub


Function TableColumnExists(ByVal ColumnName As String, ByVal Table As ListObject) As Boolean
'
' Returns True if the specified column name exists in the specified Table.
'
' Author:       Zack Barresse
'
' Syntax:       TableColumnExists(ColumnName,Table)
'
' Parameters:   ColumnName. String. Required. Column name to check if it exists.
'               Table. ListObject. Required. Table to look at.
'
    On Error Resume Next
    TableColumnExists = CBool(Table.ListColumns(ColumnName).Index <> 0)
    On Error GoTo 0

End Function


Function TableExists( _
         ByVal TableName As String, _
         Optional ByVal TableSheet As Worksheet _
         ) As Boolean
'
' Returns True if a Table exists with the specified name on the specified (or active) sheet.
'
' Author:       Zack Barresse
'
' Syntax:       TableExists(TableName,[TableSheet])
'
' Parameters:   TableName. String. Required. Table name to check if it exists.
'               TableSheet. Worksheet. Optional. Worksheet to look for Table in. The active sheet
'               is used if not specified
'
    If TableSheet Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Function
        Set TableSheet = ActiveSheet
    End If
    On Error Resume Next
    TableExists = CBool(Len(TableSheet.ListObjects(TableName).Name) <> 0)
    On Error GoTo 0

End Function


Function WorksheetExists( _
         ByVal SheetName As String, _
         Optional TargetBook As Workbook _
         ) As Boolean
'
' Returns True if the specified worksheet is found in the specified workbook.
'
' Syntax:       WorksheetExists(SheetName,[TargetBook])
'
' Parameters:   SheetName. String. Required. The name of the worksheet to test existence.
'               TargetBook. Workbok. Optional. Specify the workbook to look in. If omitted
'                   the active workbook will be used.
'
    If TargetBook Is Nothing Then
        If ActiveWorkbook Is Nothing Then Exit Function
        Set TargetBook = ActiveWorkbook
    End If
    On Error Resume Next
    WorksheetExists = CBool(Len(TargetBook.Worksheets(SheetName).Name) <> 0)
    On Error GoTo 0

End Function

The only thing the above routine doesn't do is ensure the "FuncDet" named range exists. If you want a routine for that, I typically use another function, like this:

Code:
Public Function IsExistingRangeName( _
        ByVal WorkbookOrWorksheet As Object, _
        ByVal RangeName As String _
    ) As Boolean
'
' Return True if the name exists in the workbook and is the name of a range, ' False otherwise.
'
' Syntax
'
' IsExistingName(Workbook, RangeName)
'
' Workbook - The workbook in which to look for the name.
'
' RangeName - The range name being sought.
'
' oWorksheet - The worksheet on which the worksheet level name is defined.
'   Optional. If omitted then the name is assumed to be a workbook level name.

    Dim Name As Name

    If Not TypeName(WorkbookOrWorksheet) = "Workbook" And Not TypeName(WorkbookOrWorksheet) = "Worksheet" Then
        Exit Function
    End If

    On Error Resume Next
    Set Name = WorkbookOrWorksheet.Names(RangeName)
    On Error GoTo 0

    If Not Name Is Nothing Then
        IsExistingRangeName = Name.RefersTo <> Name.RefersToR1C1
    End If

End Function

Let us know if this works. HTH
 
Upvote 0
Zack

After a couple of attempts this worked fine for me, ( my loop and some additional details I added in creating problems rather than your code) thanks for the help.

StewartS
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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