#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    52
    Rep Power
    5

    Passing ranges to subroutines in excel vba


    Hi, I'm trying to pass two ranges to a subroutine so that it can run through the cells contained in each of the ranges and sum the values therein under certain conditions. What I have is as follows:

    Code:
    With ActiveWorkbook.Worksheets("AnnL")
            calcAnnualLeave sDate, fDate, .Range("A3", Range("A3").End(xlDown)), .Range("B3", Range("B3").End(xlDown))
    End With
    
    Sub calcDate(sDate as Date, fDate as Date, r1 as range, r2 as range)
        Dim alSdate, alFdate, testDate As Range, ws As Worksheet, testValue as single, testValue2 as single         
        Set ws = ActiveWorkbook.Worksheets("AnnL")
            
        For Each testDate In r1
           testValue=testDate.value
           testValue2=testDate.offset(0,1).value
        Next testDate
    
    End Sub
    the problem is that testValue and testValue2 are always empty. I've tried everything I can think of and looked online but can't find my mistake, any help would be much appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2009
    Posts
    52
    Rep Power
    5
    Posted to different forum please see: http://www.ozgrid.com/forum/showthread.php?t=161207&p=590312&posted=1#post590312
    instead.


    Originally Posted by devshed-rob
    Hi, I'm trying to pass two ranges to a subroutine so that it can run through the cells contained in each of the ranges and sum the values therein under certain conditions. What I have is as follows:

    Code:
    With ActiveWorkbook.Worksheets("AnnL")
            calcAnnualLeave sDate, fDate, .Range("A3", Range("A3").End(xlDown)), .Range("B3", Range("B3").End(xlDown))
    End With
    
    Sub calcDate(sDate as Date, fDate as Date, r1 as range, r2 as range)
        Dim alSdate, alFdate, testDate As Range, ws As Worksheet, testValue as single, testValue2 as single         
        Set ws = ActiveWorkbook.Worksheets("AnnL")
            
        For Each testDate In r1
           testValue=testDate.value
           testValue2=testDate.offset(0,1).value
        Next testDate
    
    End Sub
    the problem is that testValue and testValue2 are always empty. I've tried everything I can think of and looked online but can't find my mistake, any help would be much appreciated.
  4. #3
  5. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Works for me after doing the following:

    You're calling a function calcAnnualLeave but the procedure you show is named calcDate

    I made a test procedure fixing that, and passing literal dates for sdate, fdate because I don't see where that's coming from in your code.

    In my test sheet I put random dates in columns A and B

    When calDate is called it correctly pulls the date value I had in A3 and assigns to TestValue



    Code:
    
    Sub calcDate(sDate As Date, fDate As Date, r1 As Range, r2 As Range)
        Dim alSdate, alFdate, testDate As Range, ws As Worksheet, testValue As Single, testValue2 As Single
        Set ws = ActiveWorkbook.Worksheets("AnnL")
            
        For Each testDate In r1
           testValue = testDate.Value
           testValue2 = testDate.Offset(0, 1).Value
        Next testDate
    
    End Sub
    
    Sub Test()
        With ActiveSheet
            calcDate "1/1/2011", "12/31/2099", .Range("A3", Range("A3").End(xlDown)), .Range("B3", Range("B3").End(xlDown))
        End With
    End Sub
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss

IMN logo majestic logo threadwatch logo seochat tools logo