You can fix VBA Runtime Error 1004 in Excel Macro, like document not found, reference isn't valid, Paste method of worksheet class failed, etc, by following this post.

Excel can solve for unknown variables, either for a single cell with Goal Seek or multiple cells with Solver.