goal seek error - vba

[Kaps_mr2]

Hi,

I am trying to replicate Excel's goal seek functionality in VBA. However, I get a 'reference is not valid' error on the line in bold:-

Set Mean_Check = main_workbook.Sheets("Sheet1").Range("mean_check")
goal_seek_parameter = main_workbook.Sheets("Sheet1").Range("Goal_seek_parameter")

Mean_Check.GoalSeek Goal:=new_mean, ChangingCell:=goal_seek_parameter

can anybody tell me why ? thanks

Kaps

 

[Andrew Poulsom]

ChangingCell should be a Range object. Try:

Set goal_seek_parameter = main_workbook.Sheets("Sheet1").Range("Goal_seek_parameter")

 

[Kaps_mr2]

thanks !!!