Using INDIRECT in VBA for Goal Seek

[JV0710]

Good Day All

Please can I get help with the following:

I am trying to do Goal Seek in VBA but I want to use the INDIRECT function to reference the cells and values

my recorded macro is as follows
*************************************************************************************
Sub mygoalseek()
'
' mygoalseek Macro
'

'
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
Range("D2").GoalSeek Goal:=52535, ChangingCell:=Range("D15")
End Sub
*****************************************************************************************
In cells K1, K2 and K3 I have cell Addresses refering to the goal seek parameters

Where I have Range("D2") in the Macro - I want to reference the cell address in K1
Where I have Goal:=52535 in the Macro - I want to reference the cell address in K2
Where I have ChangingCell:=Range("D15") in the Macro - I want to reference the cell address in K3

I think that using the INDIRECT function will be correct, but I do not know how to edit the macro accordingly

Thanks in advance for your help

 

[JV0710]

Hi

Apologies . . . to be clearer

In K1 I have typed the cell address 'D2"
In K2 I have typed the cell address 'D6" which has the number 52535
In K3 I have typed the cell address "D15'

hope it makes sense

Thanks

JVN

 

[StephenCrump]

Range(Range("K1").Value).GoalSeek Goal:=Range(Range("K2").Value).Value, ChangingCell:=Range(Range("K3").Value)

 

[JV0710]

Good Day Stephen

Thanks very much for your reply

I applied your edit

I replaced : Range("D2").GoalSeek Goal:=52535, ChangingCell:=Range("D15")
with : Range(Range("K1").Value).GoalSeek Goal:=Range(Range("K2").Value).Value, ChangingCell:=Range(Range("K3").Value)

and I get the following error messgae when running the macro

Run-time error '1004':
method 'Range' of object'_Global' failed

 

[StephenCrump]

Check your cell references, e.g. in cell K1 do you have only the two characters D2, i.e. len(K1)=2 , or have you added unnecessary inverted commas, e.g. "D2"

 

[JV0710]

Hi Stephen

I do not have any unnecessary spaces or characters in K1, K2 and K3
for info - Cells K1 to K3 are however the results of a formula that returns the addresses D2, D6 and D15

 

[StephenCrump]

Ok, next guess. Is your GoalSeek constructed properly - does D2 vary with D15?

Attached is a simple workbook, working fine for me: Box

GoalSeek.xlsm
	C	D	E	F	G	H	I	J	K
1									$D$2
2	Result -->	52535	=D15*100						D6
3									D$15
4
5
6	GOAL -->	52535
7
8
9
10
11
12
13
14
15	Change -->	525.35
1

Cell Formulas
Range	Formula
D2	D2	=D15*100
E2	E2	=FORMULATEXT(D2)
D6	D6	=52535

'Code module
Sub Test()
    
    Range(Range("K1").Value).GoalSeek Goal:=Range(Range("K2").Value).Value, ChangingCell:=Range(Range("K3").Value)

End Sub

 

[JV0710]

Hi Stephen

Thanks again for your reply

I am still getting the same error on my file. Going to rewrite the whole thing from scratch and try again . . . will feedback afterwards

Thanks again for your efforts

 

[StephenCrump]

Going to rewrite the whole thing from scratch and try again . . . will feedback afterwards

Good luck!

The workbook attached to Post#7 shows the GoalSeek working.

I don't know what else you've got going on in your workbook, but if you are able to attach a file, it should only take a minute or two to debug your GoalSeek problem.

 

[JV0710]

Good luck!

The workbook attached to Post#7 shows the GoalSeek working.

I don't know what else you've got going on in your workbook, but if you are able to attach a file, it should only take a minute or two to debug your GoalSeek problem.

Thanks very much for your help Stephen . . . It's just been such a busy week and I had no time this weekend to look at it. I will tackle it again tthis week and let you know. Once again thanks very much for your help