Subtotal

Parra

Well-known Member
Joined
Feb 21, 2002
Messages
752
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
I think you confused me more, but is this what you want:

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

and

activecell.HorizontalAlignment = xlRight
 
Upvote 0
How do I get the macro to add those formulas in column H where column L has the word total?

Thanks
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top