Using INDIRECT in VBA for Goal Seek

JV0710

Active Member
Joined
Oct 26, 2006
Messages
358
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

JV0710

Active Member
Joined
Oct 26, 2006
Messages
358
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Range(Range("K1").Value).GoalSeek Goal:=Range(Range("K2").Value).Value, ChangingCell:=Range(Range("K3").Value)
 

JV0710

Active Member
Joined
Oct 26, 2006
Messages
358
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Oct 26, 2006
Messages
358
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
CDEFGHIJK
1$D$2
2Result -->52535=D15*100D6
3D$15
4
5
6GOAL -->52535
7
8
9
10
11
12
13
14
15Change -->525.35
1
Cell Formulas
RangeFormula
D2D2=D15*100
E2E2=FORMULATEXT(D2)
D6D6=52535


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

End Sub
 

JV0710

Active Member
Joined
Oct 26, 2006
Messages
358
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Oct 26, 2006
Messages
358
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,936
Members
416,953
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top