Error 1004 "Application-defined or Object-defined error" being thrown by Names("NamedRange").RefersToRange RRS feed

  • Question

  • Hi all,

    I'm having trouble trying to figure out what's causing the following issue, so any input is welcome.

    I wrote a VB code that works perfectly fine on my machine (I know that programmers usually say this, but I really mean it this time hehe), but on another machine it's throwing the '1004' error. The two things I've noticed that are different between the machines: mine has Office 2013 and is in English, but the others have Office 2010 and are in Portuguese.

    Here's what I'm doing to create the named range:

    ActiveWorkbook.Names.Add Name:="NamedRange1", RefersTo:="=OFFSET(MySheet!$B$1,0,0,COUNTA(MySheet!$B:$B),1)"

    Later, I'm trying to access this named range by doing this:

    ThisWorkbook.ArraySubstitute2(sht.range("B" & i), ActiveWorkbook.Names("NamedRange1").RefersToRange, ActiveWorkbook.Names("NamedRange2").RefersToRange)

    The 'ArraySubstitute2' is a public static function, here's its signature:

    Public Static Function ArraySubstitute2(OrigStr As String, Rng1 As range, rng2 As range) As String

    So, what am I missing here? Can someone please advise?

    Edit 1: "MySheet" is an existing worksheet under this exact name that holds reference data just extracted from DB.

    Edit 2: The VB code works fine on all computers using English Office 2013. But if I execute the same file on a machine with PT BR Office 2010 it throws the error 1004.

    Thanks in advance!

    • Edited by DHNunes Monday, April 2, 2018 7:46 PM Adding more details
    Thursday, March 29, 2018 7:47 PM

All replies

  • DHNunes,
    re: Name problem

    My guess is that the "MySheet!" worksheet does not exist under that name in Portugal.

    Jim Cone
    Friday, March 30, 2018 12:22 AM
  • Thank you for the reply, James Cone.

    Actually, the worksheet was created at the beginning of the process, it's an extraction from a database reference table, and it's created and renamed like that.

    Sorry, I didn't add it to the original question 'cause I thought it was irrelevant, my bad. I'll update the question.

    Monday, April 2, 2018 1:53 PM
  • DHNunes,
    re: getting the sheet name

    Here is what your formula returns on my xl2010 worksheet...
    (entered as an array formula in D1:D8)

    Which raises a question...
    Are you attemping to find the contents of the last cell in column B ?
    Are you using one of the returned cells from the range ?
    Or... ?

    Jim Cone

    Monday, April 2, 2018 4:26 PM
  • Basically, the named dynamic range is being used by "ArraySubstitute2" as reference in order to replace some text per their respective Unique Ids, like this:

    So, in example above I'm using 2 named ranges ("Name" and "ID"), both created using the Formula I sent before:

    ActiveWorkbook.Names.Add Name:="Name", RefersTo:="=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)"
    ActiveWorkbook.Names.Add Name:="ID", RefersTo:="=OFFSET(MySheet!$B$1,0,0,COUNTA(MySheet!$B:$B),1)"

    And the function "ArraySubstitute2" is responsible for finding and replacing all matching text from input cell per their respective IDs, or vice-versa.

    Just a small clarification: The VB code works fine on all computers using English Office 2013. But if I execute the same file on a machine with PT BR Office 2010 it throws the error 1004.

    • Edited by DHNunes Monday, April 2, 2018 7:47 PM
    Monday, April 2, 2018 7:44 PM
  • DHNunes,
    re: problem still there

    I am not helping you with your problem.  So its time for me to bow out.
    Good luck with it.

    Jim Cone
    Monday, April 2, 2018 10:07 PM
  • No problem, James.

    Thank you for your time and help :)

    Tuesday, April 3, 2018 6:12 PM