How to resolve VBA error 2029 with #NAME?, while storing data in another workbook?

[inddon]

Hello There,

I am using two excel files.
File 1 for storing the data (as a Database) and
File 2 for data entry.

I am using the below code (in File 2) to update the corresponding table record in File 1.

In File 2, values of the range fields are as below:
EntWSDE_Header.[FStudentNumber] = 100
EntWSDE_Header.[FStudentName] = "Don"

In File 1, Table Column, it stores a value "#NAME?"

On debug, I see that it has an Error 2029

Sub UpdateData()
  Dim fnd As Range
  Dim rngSearch As Range
 
  Dim MstWB, EntWB As Workbook
  Dim MstTblName_Transactions, EntWSName_Header As String
  Dim MstWSName_Transactions, EntWSDE_Header As Worksheet 
  Dim O_MstTbl_Transactions As ListObject

  On Error Resume Next
  'Opens Master Workbook
  Set MstWB = Workbooks.Open(Filename:=ABC.xlsx, ReadOnly:=False, Notify:=False)

  'Set Master Workbook and Worksheet
  Set MstWSName_Transactions = MstWB.Worksheets("Transactions")
  MstTblName_Transactions = "Tbl_Transactions"
  Set O_MstTbl_Transactions = MstWSName_Transactions.ListObjects(MstTblName_Transactions)

  'Set Entry Workbook and Worksheet
  Set EntWB = ThisWorkbook
  EntWSName_Header = "Header"
  Set EntWSDE_Header = EntWB.Worksheets(EntWSName_Header)

  'Set Master Workbook Table Range and Search Value Student Number
  Set rngSearch = MstWSName_Transactions.ListObjects("Tbl_Transactions").Range
  Set fnd = rngSearch.Find(What:=EntWSDE_Header.[FStudentNumber].Value, LookIn:=xlValues, Lookat:=xlWhole)
 
   'If record found in Master Table
   'Assign Value from Entry to Master Worksheet Table
  If Not fnd Is Nothing Then
      '-----------------------------------------------------------------------------
      'This is the assignment which updates Table Column "Student Name
      'With a value #NAME?
      '-----------------------------------------------------------------------------
     Intersect(O_MstTbl_Transactions.ListColumns("Student Name").DataBodyRange, fnd.EntireRow).Value = EntWSDE_Header.[FStudentName]
    ....
    .....
    ....
    End if
End Sub

Could you please advise, how this can be resolved?

Many thanks & regards,
Don

 

[Chihiro]

Always upload sample workbook, clearly indicating what the issue is, as well as your expected output.

 

[inddon]

Thank you Chihiro for your reply.

Due to confidentiality, I cannot upload the actual workbooks. Therefore, I listed the code snippet in my post.

I am attaching a few snapshots of the sequence of the data entered and queried from the Table

1. Data Entered in Worksheet.JPG
The cell format is General

2. Data Stored in Table Tbl_Student.JPG

This is the code used to update the table column
Intersect(O_MstTbl_Transactions.ListColumns("Student Name").DataBodyRange, fnd.EntireRow).Value = EntWSDE_Header.[FStudentName]

3. Data Queried from Table Tbl_Student into Worksheet.JPG

All the other cell insert/update goes okay


I came across this link which is close to the problem I am facing but not the solution:
https://stackoverflow.com/questions/11760561/how-to-resolve-excel-error-2029-with-name

Thanks again

Regards,
Don

 

[Chihiro]

Sanitize the workbook and keep only the bare minimum needed to demonstrate your issue. We don't need any of confidential part to help you.

Typically, speaking you'd not want any error value stored in data range (except when you want to use it as visual inspection tool for validating process).

I assume it's from formula. If so, just nest it in IFERROR() formula.

 

[inddon]

Thank you Chihiro.

I will work and get the workbook for the demo purpose. Will post it

Regards,
Don

 

[inddon]

Sanitize the workbook and keep only the bare minimum needed to demonstrate your issue. We don't need any of confidential part to help you.

Typically, speaking you'd not want any error value stored in data range (except when you want to use it as visual inspection tool for validating process).

I assume it's from formula. If so, just nest it in IFERROR() formula.

I checked the NamedRange again, it is not a formula.

I was preparing the file for demo purpose, and the code worked fine. There was no error saved as #NAME?

I tried again with the problematic code in the original workbook. I performed the following:
1. Deleted and recreated the RangeName - Didn't work
2. Tried a different cell address with the same RangeName - Didn't work
3. Deleted and created a new RangeName with a new Name - It just worked

It's very weird and I don't understand. But it worked I am glad


Thank you Chihiro for offering to take a look at the file.

Regards,
Don

 

[Chihiro]

So there was some conflict, or some violation in the name

Glad you got it solved.