Solved: goal seek w/ value from another workbook

[DevanG]

error message: Reference is not valid.

All the ranges i'm looking at are filled (not null). Is there some rule i'm missing here?

[VBA]
Dim Summary As String
Dim DCM As Workbook

DCM.Sheets("Case Data Entry").Range("C9").GoalSeek goal:=Workbooks(Summary).Sheets(1).Range("J" & i).Value, ChangingCell:=DCM.Sheets("Case Data Entry").Range("C7")[/VBA]

[Kenneth Hobs]

Can you even do that manually or on one row?

You did not Set DCM so that would naturally cause the problem. No i value was set. I know that you snipped what you needed to post but partial code leaves gaps for us that may be significant to helping you.

You may be better off copying the value to one workbook on one sheet and then running goalseek from the workbook and worksheet with all that information. You can always delete the ones that you copy.

Here is an example but all of my data was on the activesheet.
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Long
If Target.Count > 1 Or Target.Column <> 10 Then Exit Sub
r = Target.Row
Application.EnableEvents = False
On Error Resume Next
Range("H" & r).GoalSeek Goal:=Range("I" & r).Value, ChangingCell:=Range("A" & r)
Application.EnableEvents = True
End Sub[/VBA]

[sake14]

Thank you for the reply and help...but it seems that my excel has some more general problem with the goal seek in vba. I tried it even in its simplest form and subs but I keep getting the same message...When I remove the goal seek from the code everything else works just fine....