+ Reply to Thread
Results 1 to 4 of 4

Set Range syntax - currently causing #value! error sometimes

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    225

    Set Range syntax - currently causing #value! error sometimes

    Dim knownX1 As Range '3 nm value range for first intersection ex.(470,480,490)
    Dim knownY1 As Range    '3 corresponding chromaticity values for the above wavelengths
    Dim knownX2 As Range    '3 nm value range for second intersection ex.(570,580,590)
    Dim knownY2 As Range    '3 corresponding chromaticity values for the above wavelengths
    Set knownX1 = Sheets("references").Range("b" & wavelength1 - 1 & ":b" & wavelength1 + 1)    'uses the nm point values to define 3 nm range for 2nd degree polynomial range. example: 450,460,470 will be the range
    Set knownY1 = Sheets("references").Range("bz" & wavelength1 - 1 & ":bz" & wavelength1 + 1)  'returns the corresponding chromaticity valuese for the illuminant of these 3 nm points.
    Set knownX2 = Sheets("references").Range("b" & wavelength2 - 1 & ":b" & wavelength2 + 1)    'same but for intersection 2
    Set knownY2 = Sheets("references").Range("bz" & wavelength2 - 1 & ":bz" & wavelength2 + 1)  'ditto
    i have a situation where I am troubleshooting a user function that returns #value! errors when this block of code is in, however when i comment it out, it will return a basic value like

    function output(x as integer)
    block above
    output = x
    end function
    obviously there is other stuff in there. am i referring to this range incorrectly? this block of code works perfectly when the user inputs of my function create a specific type of result but not others. when i tried to step into this function via a sub, i would get overflow errors on the first calculation (that was simple division).

    any ideas?

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Set Range syntax - currently causing #value! error sometimes

    Maybe:

    Function output(x As Integer) As Integer
    block above
    output = x
    End Function
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    01-06-2011
    Location
    cleveland
    MS-Off Ver
    Excel 2007
    Posts
    225

    Re: Set Range syntax - currently causing #value! error sometimes

    it had something to do with the variable declarations - i just reworked the code to no longer need those references and everything is proper now. thank you for your help

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Set Range syntax - currently causing #value! error sometimes

    You're welcome and thanks for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Selecting range causing error
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2014, 02:24 PM
  2. Out of range error Possible syntax error?
    By Rob K in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-02-2014, 06:30 AM
  3. [SOLVED] Using Range.clearcontents causing type mismatch error in code
    By Captian LDS in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2014, 04:54 PM
  4. -2 for shapetype causing subscript out of range error.
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2011, 05:31 PM
  5. Syntax Error adding Date Picker to a range
    By B. Baumgartner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2007, 12:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1