Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Assign Range To Variable

  1. #1
    Join Date
    24th December 2007
    Posts
    2

    Assign Range To Variable

    Hi

    I am trying to assign a range of cells to a variable but it isn't working. My code is as follows:

    VB:
    Dim VarRateTable As Range 
    
    
    VB:
    Private Sub CalcBudgets(rng As String) 
         
         '  Dim VarRateTable As Range
        Dim CurrentRng As Range 
        Set CurrentRng = Selection 
        Set VarRateTable = Range(rng) 
    
    
    VB:
    Private Sub NEWCALC(datarow) 
         
        Dim vt As Double 
         
        vt = Application.WorksheetFunction.VLookup(Cells(datarow, 4).Value, VarRateTable, 3, 0) 
    
    
    but the Set statement gives me an error:

    <method 'range'of object '_global' failed>

    Any ideas what i might be doing wrong?

    many thanks

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,512

    Re: Assigning A Range To A Variable

    Its honestly difficult to say without seeing your spreadsheet and / or the rest of your code. For example, this is your code, which I managed to get work and I didnt get any errors with the set statements....

    VB:
    Dim VarRateTable As Range 
     
    Public Sub caller() 
         
        CalcBudgets ("$A$1:$D$4") 
         
         
    End Sub 
     
    Public Sub CalcBudgets(rng As String) 
         
         '  Dim VarRateTable As Range
        Dim CurrentRng As Range 
        Set CurrentRng = Selection 
        Set VarRateTable = Range(rng) 
         
        MsgBox CurrentRng.Address 
        MsgBox VarRateTable.Address 
         
        NEWCALC (CurrentRng.Columns(1)) 
         
    End Sub 
     
    Public Sub NEWCALC(datarow) 
         
        Dim vt As Double 
        MsgBox VarRateTable.Address 
         
        vt = Application.WorksheetFunction.VLookup(Cells(datarow, 4).Value, VarRateTable, 3, 0) 
         
         
    End Sub 
    
    
    Couple of points:
    1) Make sure something is selected for the line of code
    VB:
    Set CurrentRng = Selection 
    
    
    2) make sure there are sufficient columsn in the VLOOKUP table... your looking up the third column, so check the address of the table like I did in the example tomake sure your table is at least 3 columns wide, from the first column you look up.
    3) Make sure your data type is correct for the vlookup, you are returning a "double", is there a double stored in third column of the lookup?

    Other than that,I'm not sure what could be wrong with the code, but debug like I have done using message boxs to show the address of the range that you want to work on.

    HTH, happy xmas.

    Ger
    Auto Merged Post;

    Additional thought:

    try:

    VB:
    msgbox selection.address 
    
    
    before

    VB:
    Set CurrentRng = Selection 
    
    
    To make sure something is selected.

    Ger
    Last edited by Wigi; December 24th, 2007 at 22:52. Reason: code tag added
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  3. #3
    Join Date
    24th December 2007
    Posts
    2

    Re: Assigning A Range To A Variable

    Thank you very much Ger, and a Happy Xmas to you
    regards
    Dave

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Assign Quotes To Variable
    By Jay@AFOP in forum EXCEL HELP
    Replies: 3
    Last Post: October 13th, 2007, 15:39
  2. Assign Variable To Different Data Types
    By rowfast7 in forum EXCEL HELP
    Replies: 2
    Last Post: October 12th, 2007, 14:35
  3. Assign Variable to Textbox
    By Red2034 in forum Excel and/or Powerpoint Help
    Replies: 4
    Last Post: July 29th, 2007, 05:19
  4. Assign Variable To Selected Range
    By Fred Kienert in forum EXCEL HELP
    Replies: 2
    Last Post: December 22nd, 2006, 07:48
  5. assign name of the active range to a variable?
    By stevesage in forum EXCEL HELP
    Replies: 6
    Last Post: December 24th, 2004, 07:32

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
  •  
porno