??? Variable = activesheet.range(selection.address) ???

[dukeofscouts]

So I'm working on a macro that will utilize GoalSeek to make calculations faster.

Working code....

Dim CellToChange As Range
'Trouble Starts
CellToChange = Sheet4.Range(Selection.Address)
Call maxoutform.FindMyNutritent(ComboBox1.Value)
Selection.Offset(0, -3).Select
Selection.GoalSeek goal:=0.01, changingcell:=CellToChange

Working Code...

Sub FindMyNutritent(nameMyNutrient As String)
Range("a7").Select
While Selection.Value <> nameMyNutrient
Selection.Offset(0, 1).Select
End Sub

 

[Damon Ostrander]

Hi dukeofscouts,

Your statement CellToChange = Sheet4.Range(Selection.Address) needs to be

Set CellToChange = Sheet4.Range(Selection.Address)

because CellToChange is an object variable. Set is always required when assigning an object to an object variable.

That being said, since Selection in this context is clearly a Range object you could simply do:

Set CellToChange = Selection

I'm assuming the sheet containing your desired selection is the active worksheet (actually, your code is assuming this).

Damon

 

[dukeofscouts]

THANKS!!!!!
PERFECTION...

Well almost. I get a run-time error on the goal seek. In debug mode it shows that the variable value is set to the value of the selection. I've done it both with the "selection.address" version and the strait "selection". With the variable set as a range I'm at a loss for why it would change the variable to be mixed.

If OptionNutrient.Value = True Then
Dim CellToChange As Range
Set CellToChange = Selection
Call maxoutform.FindMyNutritent(ComboBox1.Value)
Selection.Offset(0, -3).Select
Selection.GoalSeek goal:=0.01, changingcell:=CellToChange

'Error 1004
'Reference not valid
​

End If