Update named cell values

Question

I have a workbook with many named cells in different worksheets. I'm trying to write a VBA script that will read an external .csv file to extract a variable name and variable value, so that I can update the named cell values in my workbook.

I'm able to read and loop through the data in the CSV file but I am unable to update the named values.

Ideally, the script would check the variable name is a valid named cell in the workbook then update with the new value as defined in the .csv file.

I've run a number of iterations but the gist of the code is:

Public Sub readCSV()
'
' VBA script to read external CSV file
'
'

Dim filePath As String
Dim inFilePath As String
Dim inCase As String

strWorkBook = ActiveWorkbook.Name
filePath = Range("aString").Value
tmpsep = InStrRev(filePath, "\")

inCase = Right(filePath, Len(filePath) - tmpsep)
inFilePath = Left(filePath, Len(filePath) - Len(inCase))


' Check that path is valid and exit if not
    Range("aString").Select
    If IsEmpty(ActiveCell.Value) Then
        MsgBox "ERROR! No Input File Defined - Exiting!"
        Range("H7").Select
        End
    End If

' Open data file
Workbooks.Open Filename:=filePath


' Loop through variable names in input file
varNamCol = "C"
varColNum = "D"

    ' Ensure we're in input file
    Windows(inCase).Activate

    ' Find last row input file - Call separate routine (working)
    Call FindLastRow.FindLastRow(lRow)

    i = 1
    imax = lRow



    Do While i <= imax
            Windows(inCase).Activate
            ' Read Variable Name and Value from csv
                inVarName = Range(varNamCol & I).Value
                inVarValue = Range(varColNum & I).Value

                If IsEmpty(inVarName) Then
                    MsgBox " Variable is empty - Moving On"
                    GoTo NextIteration
                Else
                   Windows(strWorkBook).Activate
                   Range(inVarName).Value = inVarValue
                End If


        NextIteration:
        i = i + 1
        Loop
End Sub

Leverage Leverage · Accepted Answer · 2018-05-31T05:50:16

Issue resolved by performing the operations the other way around. Instead of reading the input deck and trying to find the corresponding named range in the target workbook, I loop through the named ranges and find the corresponding values from the input deck.

I have also included the Application.EnableEvents commands to prevent embedded Worksheet_Change macros from activating while updating values.

The code is:

Sub tmp()


Dim filePath As String
Dim inFilePath As String
Dim inCase As String

On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.EnableEvents = False



'----------------------------------
' Find path for input file
    strWorkBook = ActiveWorkbook.Name

    filePath = Range("aString").Value
    tmpsep = InStrRev(filePath, "\")

    ' Input file workbook name
    inCase = Right(filePath, Len(filePath) - tmpsep)
    'Input file full path
    inFilePath = Left(filePath, Len(filePath) - Len(inCase))

' Check that path is valid and exit if not
    Range("aString").Select
    If IsEmpty(ActiveCell.Value) Then
        MsgBox "ERROR! No Input File Defined - Exiting!"
        Range("H7").Select
        End
    End If

' Open input data file
    Workbooks.Open Filename:=filePath

'-------------------------------------



    Dim rFind As Range

' Process to update name values
    Windows(strWorkBook).Activate

    For Each nm In ActiveWorkbook.Names
        varname = nm.Name
        varsheet = Range(nm).Parent.Name
        varcell = nm.RefersToRange.Address(False, False)

        Sheets(varsheet).Select
        Range(varcell).Select

'      Ensure variable in Home and HiddenVariables are not over-written
        If varsheet = "Home" Or varsheet = "HiddenVariables" Then
            GoTo NextIteration
        End If

'      Omit non-user input variables cbelts, anrz, anumhxc, nrotzone

        If varname = "cbelts" Or varname = "anrz" Or varname = "anumhxc" Or varname = "nrotzone" Then
            GoTo NextIteration
        End If

'           Selection.ClearContents

        Windows(inCase).Activate

        ' Find range in inCase that matched varName
            With Range("C:C")
                Set rFind = .Find(What:=varname, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

                If Not rFind Is Nothing Then
                    inCaseRow = rFind.Row
                    updateVal = Range("D" & inCaseRow).Value

                    Windows(strWorkBook).Activate
                    Sheets(varsheet).Select
                    Range(varcell).Value = updateVal
                    Range("D4").Select

                Else
                    Windows(strWorkBook).Activate
                    Range("D4").Select
                End If
            End With


NextIteration:

    Next nm


'  Include routines to populate Porous Media inputs





Application.ScreenUpdating = True

'       Close input case file
        Windows(inCase).Activate
        ActiveWindow.Close

ErrorHandler:
Application.EnableEvents = True


End Sub

Update named cell values

1 Answers