Subtotal
Subtotal
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Subtotal

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I created a macro that ends by creating a subtotal for some data. What I then want is to add a vlookup function for each subtotal "Total" in column L, I want the vlookup in column H. So it will go something like this, =vlookup(L7,GLNumber,2,false). How do I get the formula in column H and have it align right?

    Please Help Thanks

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you mean by this line:
    On 2002-04-17 10:31, Parra wrote:
    How do I get the formula in column H and have it align right?

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,854
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-17 10:31, Parra wrote:
    I created a macro that ends by creating a subtotal for some data. What I then want is to add a vlookup function for each subtotal "Total" in column L, I want the vlookup in column H. So it will go something like this, =vlookup(L7,GLNumber,2,false). How do I get the formula in column H and have it align right?

    Please Help Thanks
    Column L from L7 seems to house subtotals computed with a macro.

    What is GLnumber?

    Apparently you want a lookup formula that must go in H. Right?

    The picture is not clear (to me). Care to elaborate a bit more?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry if I was not clear I'll try to explain better.

    After the macro is done adding subtotals to the data. It summerizes the data by automatically writing total under each different subtotal. This information appears in column N, this is the last column for my spreadsheet. In column H of the subtotal row, I want a vlookup function to lookup column N and return a certain value. Because there is text to the right of this cell I would like it to "align right" so that if the value looked up is big it will not be cut off.

    What I am tring to do is get a General Ledger Number for each of the subtotals.

    Please let me know if I need to give more info.

    Thanks

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is what I need but I don't know how to do it, if Column L has the word Total add the vlookup formula to Column H.

    I know to do these things but not in a macro format.

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think you confused me more, but is this what you want:

    activecell.formula="=vlookup(L7,GLNumber,2,false)"

    and

    activecell.HorizontalAlignment = xlRight
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How do I get the macro to add those formulas in column H where column L has the word total?

    Thanks

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:
    Code:
    For Each cell In Intersect(UsedRange, Columns(12))
    If LCase(cell.Value) = "total" Then
    With cell.Offset(0, -4)
        .Formula = "=vlookup(L7,GLNumber,2,false)"
        .HorizontalAlignment = xlRight
    End With
    End If
    Next
    _________________
    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-04-17 11:40 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It looks good and I added it but I got the following error message run time error '424 object required.

    When I clicked on debug the first line of your macro was highlighted in yellow.

    Also will the vlookup formula change depending on which row it is put on?

    Thanks for all the help

  10. #10
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Try the following code (worked for me):
    Code:
    Sub RunVlookup()
    Dim GLNumber As Range
    Set GLNumber = Range("L1: M30")
    For Each cell In Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(12))
    If LCase(cell.Value) = "total" Then
    With cell.Offset(0, -4)
        .Formula = "=Vlookup(" & cell.Address(rowabsolute:=False, columnabsolute:=False) _
        & "," & GLNumber.Address & ",2,false)"
        .HorizontalAlignment = xlRight
    End With
    End If
    Next
    
    End Sub
    Change GLNumber to whatever range you want.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com