Announcement

Collapse
No announcement yet.

Assign Range To Variable

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Code:
    Dim VarRateTable As Range
    Code:
     Private Sub CalcBudgets(rng As String)
    
      '  Dim VarRateTable As Range
        Dim CurrentRng As Range
        Set CurrentRng = Selection
        Set VarRateTable = Range(rng)
    Code:
    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

  • #2
    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....

    Code:
    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
    Code:
    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:

    Code:
    msgbox selection.address
    before

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

    Ger
    Last edited by Wigi; December 24th, 2007, 23:52. Reason: code tag added

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    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

    _______________________________________________

    Comment


    • #3


      Re: Assigning A Range To A Variable

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

      Comment

      Working...
      X