Code Isn't Updating Range with Formula

[antman2988]

Hello! For some reason, my code below isn't updating the range with the formula specified. I've tried

.Formula

, but I still get the same result. Please help!

Sub UpdateCrosswalkData()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim book As Workbook
    Dim ws As Worksheet
    Dim closedBook As Workbook
  
    Set book = ActiveWorkbook
    Set ws = book.Worksheets(1)
    Set closedBook = Workbooks.Open("c:\\TEMPLATE.xlsx")
  
    closedBook.Worksheets(1).Copy After:=book.Sheets(2)
    closedBook.Close SaveChanges:=False
  
    'Declare constants to search for
    Const searchProgName As String = "Program Name"
    Const searchProj As String = "Project"
  
    Call UpdateColumns(searchProj, searchProgName, ws)
  
    ws.Activate
  
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic

End Sub

Sub UpdateColumns(ByVal proj As String, ByVal progName As String, ByVal ws As Worksheet)
    Dim foundprog As Range
    Dim foundproj As Range
    Dim col As Long
    Dim row As Long
    Dim colProj As String
    Dim rowProj As Long
    Dim i As Range
    Dim lastRow As Long
    Dim masterSheet As String
  
    'Attempt to find string values in table header row, get column of found cell
    On Error Resume Next
    Set foundprog = ws.Rows(1).Find(progName, LookAt:=xlWhole)
    col = foundprog.Column
    row = foundprog.row
    Set foundproj = ws.Rows(1).Find(proj, LookAt:=xlWhole)
    colProj = Split(foundproj.Address(1, 0), "$")(0)
    rowProj = foundproj.row
    On Error GoTo 0
  
    masterSheet = INDEXSheetName()
    lastRow = ws.Cells.Find(What:="*", _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
  
    For Each i In Range(Cells(row + 1, col), Cells(lastRow, col)).Cells
        i.Value = "=INDEX('" & masterSheet & "'!$P:$P,MATCH('" & ws.Name & "'!" & colProj & i.row & ",'" & masterSheet & "'!$B:$B,0))"
    Next i
End Sub

Function INDEXSheetName()
    Dim sheet As Worksheet
  
    'Loop through sheets to find project master sheet
    For Each sheet In ActiveWorkbook.Worksheets
        If sheet.Name Like "*Project Master*" Then
            INDEXSheetName = sheet.Name
            Exit For
        End If
    Next sheet
End Function

 

[antman2988]

Moving the code to copy a sheet from another workbook to a new submodules solved the issue. Any idea why?

Option Explicit

'This macro will open a specified closed book and copy its sheets to the ActiveWorkbook
Sub UpdateCrosswalkData()
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim book As Workbook
    Dim ws As Worksheet
    
    Set book = ActiveWorkbook
    Set ws = book.Worksheets(1)
    
    Call CopyWorkbook(book)
    
    'Declare constants to search for
    Const searchProgName As String = "Program Name"
    Const searchProj As String = "Project"
    
    ws.Activate
    
    Call UpdateColumns(searchProj, searchProgName, ws)
    
    book.Save
    
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub

Sub UpdateColumns(ByVal proj As String, ByVal progName As String, ByVal ws As Worksheet)
    Dim foundprog As Range
    Dim foundproj As Range
    Dim col As Long
    Dim row As Long
    Dim colProj As String
    Dim rowProj As Long
    Dim i As Range
    Dim lastRow As Long
    Dim masterSheet As String
    
    'Attempt to find string values in table header row, get column of found cell
    On Error Resume Next
    Set foundprog = ws.Rows(1).Find(progName, LookAt:=xlWhole)
    col = foundprog.Column
    row = foundprog.row
    Set foundproj = ws.Rows(1).Find(proj, LookAt:=xlWhole)
    colProj = Split(foundproj.Address(1, 0), "$")(0)
    rowProj = foundproj.row
    On Error GoTo 0
    
    masterSheet = INDEXSheetName()
    lastRow = ws.Cells.Find(What:="*", _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).row
    
    For Each i In Range(Cells(row + 1, col), Cells(lastRow, col)).Cells
        i.Value = "=INDEX('" & masterSheet & "'!$P:$P,MATCH('" & ws.Name & "'!" & colProj & i.row & ",'" & masterSheet & "'!$B:$B,0))"
    Next i
End Sub

Function INDEXSheetName()
    Dim sheet As Worksheet
    
    'Loop through sheets to find project master sheet
    For Each sheet In ActiveWorkbook.Worksheets
        If sheet.Name Like "*Project Master*" Then
            INDEXSheetName = sheet.Name
            Exit For
        End If
    Next sheet
End Function

Sub CopyWorkbook(ByVal book As Workbook)
    Dim closedBook As Workbook
    
    Set closedBook = Workbooks.Open("C:\\Template.xlsx")
    
    closedBook.Worksheets(1).Copy After:=book.Sheets(2)
    closedBook.Close SaveChanges:=False
End Sub

 

[DanteAmor]

    ws.Activate
    Call UpdateColumns(searchProj, searchProgName, ws)

But you also reversed those lines.
That means you are activating the ws sheet
And that helps for this line:

For Each i In Range(Cells(row + 1, col), Cells(lastRow, col)).Cells

Since in that line of code you don't have the sheet references.
It should be something like this:

For Each i In ws.Range(ws.Cells(row + 1, col), ws.Cells(lastRow, col)).Cells

 

[antman2988]

    ws.Activate
    Call UpdateColumns(searchProj, searchProgName, ws)

But you also reversed those lines.
That means you are activating the ws sheet
And that helps for this line:

For Each i In Range(Cells(row + 1, col), Cells(lastRow, col)).Cells

Since in that line of code you don't have the sheet references.
It should be something like this:

For Each i In ws.Range(ws.Cells(row + 1, col), ws.Cells(lastRow, col)).Cells

Ohhh, thank you. I don't know why I didn't put the worksheet reference. I think I was assuming it was fine since I was using the variables, but obviously that would just return column and row numbers rather than a reference to the defined worksheet since I didn't define the variables with the worksheet reference. I had originally written another piece of code that included the worksheet reference, but needed to change the functionality and forgot to add the reference back in. Thanks for the help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.