Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range (VBA (Excel 97))

    I have a range named "month" in cells a1:a12 and I would like to be able to associate certain other public variables to a particular month name in the range. For example, I would like values located in columns b and c to be associated with the same row as "month". I would like to be able to update this page as the month changes.

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range (VBA (Excel 97))

    I can't understand what you're trying to accomplish - could you expand a bit with a concrete example?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range (VBA (Excel 97))

    In sheet one, column (a1:a12) contains the names of the months. Let column "b" (in sheet one) be revenue balances for a particular month and this column is currently empty. This data (revenue) is generated from another module. I have the function in the revenue module set up so that revenue balance is defined as a public variable. When I update sheet one, I would like for revenue balance (for whatever month that it was created in) to pass to the same month in sheet one (i.e., column [img]/forums/images/smilies/cool.gif[/img]. If the revenue balance was created in May for $3MM, then an updated sheet one will show $3MM in column b to the right of "May".

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range (VBA (Excel 97))

    Sorry - i'm still not clear. You say "When I update sheet one, I would like for the revenue balance ... to pass to the same month in sheet one." - don't you mean when you update the revenue balance? Is this done in the same workbook or a different one?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range (VBA (Excel 97))

    Does Column A contain the month name as a string, or does it contain date values formatted as month names? In your VBA code, do you have a variable that contains the month that corresponds to the revenue? Is it a string with the month name, or a date value? What is the variable name? What is the variable name of the variable that contains the revenue?

    Maybe the code below will get you started. It assumes that column A contains month names as strings, the VBA code has a variable named srtMonth which contains the month name as a string, and a variable name dRevenue that contains the revenue.

    <pre>Dim I As Long
    For I = 0 To 11
    If Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = strMonth Then
    Worksheets("Sheet1").Range("B1").Offset(I, 0).Value = dRevenue
    Exit For
    End If
    Next I
    If I > 11 Then
    MsgBox "Unrecognized Month"
    End If
    </pre>

    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range (VBA (Excel 97))

    I had MonthName defined as

    MonthName = Array("January", "February", ... , "December") and I used this array to place the months in sheet one.

    I want strMonth (entered as the month for revenue balance in another module and defined as a public variable in that module) to associate with the correct MonthName and place the value of revenue in column b of the correct month's name. I had started with the following code:

    With range ("b3:b14") 'MonthName range
    Set c = .Find(strMonth, LookIn:xlValues)
    If Not c is Nothing Then
    firstAddress = c.Address
    Do

    This is the point at which I don't know where to go. Would I just need to add "if" statements to get it to do what I want?

Posting Permissions

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