HelpPlease715
New Member
- Joined
- Aug 12, 2019
- Messages
- 1
Hello. I am a bit new at using VBA for complex and detailed codes and I have really only used it for simple basic needs. I would say I have an average to above average understanding of it though.
I am working on project in Excel where a User Form will open and display a Total Forecast Amount along with the Forecast Amount by specific Territories. Then to the right of it will be a Total Adjustment Amount, slider bars and Adjusted Amount by specific territories. This will allow the user to see what the total amount is and increase/decrease from there. Right now the Total Forecast Amount and Total Adjustment Amount are being pulled by looking up the value in a specific cell. However, for the specific Forecast Amount by Territories I need to utilize SumIf/Index/Match and lookup the amount based on the territory name, year, month, etc. I keep getting a few different error codes but the current one is "unable to get the Index property of the worksheet function class.
Below is a portion of the code, please help me figure out what I am doing wrong. Any advice is much appreciated! Also, I am new to posting in these forums but I have another question regarding the slider and it's use. Do I create a new post or can I include it in this thread once this issue is resolved?
Sub ShowUserForm1()
TheAdjustment = ActiveCell.Value
TheSubFamily = Cells(ActiveCell.Row, 1)
Site = Cells(1, 1)
UserForm1.TotalFcstAmount.Value = TheForecast
UserForm1.TotalFcstAmount.Value = Format(UserForm1.TotalFcstAmount.Value, "$#,##0")
UserForm1.TotalAdjAmount.Value = TheAdjustment
UserForm1.TotalAdjAmount.Value = Format(UserForm1.TotalAdjAmount.Value, "$#,##0")
RegionFcstAmt1 = Application.WorksheetFunction.SumIfs(Application.WorksheetFunction.Index(Worksheets("REV DATA").Range("$AK:$BH"), 0, Application.Match(ForecastDate, Worksheets("REV DATA").Range("$AK$3:$BH$3"), 0)), Worksheets("REV DATA").Range("$A:$A"), Worksheets("TOTAL CHANGES").Range("$A6"), Worksheets("REV DATA").Range("$D:$D"), Worksheets("TOTAL CHANGES").Range("$C6"), Worksheets("REV DATA").Range("$E:$E"), Worksheets("TOTAL CHANGES").Range("$D6:$F6"))
UserForm1.Show
End Sub
I am working on project in Excel where a User Form will open and display a Total Forecast Amount along with the Forecast Amount by specific Territories. Then to the right of it will be a Total Adjustment Amount, slider bars and Adjusted Amount by specific territories. This will allow the user to see what the total amount is and increase/decrease from there. Right now the Total Forecast Amount and Total Adjustment Amount are being pulled by looking up the value in a specific cell. However, for the specific Forecast Amount by Territories I need to utilize SumIf/Index/Match and lookup the amount based on the territory name, year, month, etc. I keep getting a few different error codes but the current one is "unable to get the Index property of the worksheet function class.
Below is a portion of the code, please help me figure out what I am doing wrong. Any advice is much appreciated! Also, I am new to posting in these forums but I have another question regarding the slider and it's use. Do I create a new post or can I include it in this thread once this issue is resolved?
Sub ShowUserForm1()
TheAdjustment = ActiveCell.Value
TheSubFamily = Cells(ActiveCell.Row, 1)
Site = Cells(1, 1)
UserForm1.TotalFcstAmount.Value = TheForecast
UserForm1.TotalFcstAmount.Value = Format(UserForm1.TotalFcstAmount.Value, "$#,##0")
UserForm1.TotalAdjAmount.Value = TheAdjustment
UserForm1.TotalAdjAmount.Value = Format(UserForm1.TotalAdjAmount.Value, "$#,##0")
RegionFcstAmt1 = Application.WorksheetFunction.SumIfs(Application.WorksheetFunction.Index(Worksheets("REV DATA").Range("$AK:$BH"), 0, Application.Match(ForecastDate, Worksheets("REV DATA").Range("$AK$3:$BH$3"), 0)), Worksheets("REV DATA").Range("$A:$A"), Worksheets("TOTAL CHANGES").Range("$A6"), Worksheets("REV DATA").Range("$D:$D"), Worksheets("TOTAL CHANGES").Range("$C6"), Worksheets("REV DATA").Range("$E:$E"), Worksheets("TOTAL CHANGES").Range("$D6:$F6"))
UserForm1.Show
End Sub
