Subtotal

[Parra]

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

[Al Chara]

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?

[Aladin Akyurek]

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?

[Parra]

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

[Parra]

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.

[Al Chara]

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

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

and

activecell.HorizontalAlignment = xlRight

[Parra]

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

Thanks

[Al Chara]

Try the following 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 ]

[Parra]

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

[Al Chara]

Try the following code (worked for me):

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.