kathleen422
New Member
- Joined
- Jan 16, 2004
- Messages
- 40
I have Budget Data (12 months worth) on a sheet named Lookup and I have actual data by month on the sheet named Actual
Actual Data can be from 1 - 12 months, data is laid out
Acct Mth1 Mth2 Mth3 ..... ----> mth12
I can find out where the Actual Data ends by doing a Selection.end(xlToRight) and then calculating what Column I'm in on the Actual Sheet. I then need to go to the Lookup Sheet and return the amount in that column tto get the budgeted amount.
Dim CurrentMonth as Integer
Range("A1").select
Selection.end(xltoRight).select
CurrentMonth= Activecell.column
By thought pattern was I should then be able to apply the following formula:
LOOKUP is the sheet where the data resides
AP2 is where the Acct Number resides that I am looking up on
=IF(ISERROR(VLOOKUP(AP2,LOOKUP!$A$1:$N$10000,CurrentMonth,0)),0,VLOOKUP(AP2,LOOKUP!$A$1:$N$10000),CurrentMonth,0))
By now I guess you have figured out that it is not working with the declared variable. The ending column can vary monthly so I can't hardcode it
This formula is being used to go find the Budget Amount for that Month in the Lookup sheet
Appreciate any help you might be able to provide.
Thanks,
Actual Data can be from 1 - 12 months, data is laid out
Acct Mth1 Mth2 Mth3 ..... ----> mth12
I can find out where the Actual Data ends by doing a Selection.end(xlToRight) and then calculating what Column I'm in on the Actual Sheet. I then need to go to the Lookup Sheet and return the amount in that column tto get the budgeted amount.
Dim CurrentMonth as Integer
Range("A1").select
Selection.end(xltoRight).select
CurrentMonth= Activecell.column
By thought pattern was I should then be able to apply the following formula:
LOOKUP is the sheet where the data resides
AP2 is where the Acct Number resides that I am looking up on
=IF(ISERROR(VLOOKUP(AP2,LOOKUP!$A$1:$N$10000,CurrentMonth,0)),0,VLOOKUP(AP2,LOOKUP!$A$1:$N$10000),CurrentMonth,0))
By now I guess you have figured out that it is not working with the declared variable. The ending column can vary monthly so I can't hardcode it
This formula is being used to go find the Budget Amount for that Month in the Lookup sheet
Appreciate any help you might be able to provide.
Thanks,