Selected cell move one column to the right

**marksuza** · 08-13-2007, 07:02 AM

Hi,

I have the following piece of code:

Set IRR = Application.InputBox( _
prompt:="Select IRR Cell", Type:=8)

Range("F8").GoalSeek Goal:=IRR, ChangingCell:=Range("F7")

Assume the user selected the IRR cell to be F8, how do I change it to be G8, move one to the right, at the end of the code? And one down as well F9 (this is for a different variable I will create later).

Thanks in advance for the help.

Regards,

Marcos

**dominicb** · 08-13-2007, 08:34 AM

Good afternoon marksuza

Have a look at the code below. I have renamed your IRR variable to NewIRR - there is already a VBA instruction called IRR, and this will cause havoc with your code, throwing errors up and all sorts :

Please Login or Register  to view this content.

You don't have to activate it - I have, but you can capture the address if you'd prefer for further manipulation.

HTH

DominicB

**marksuza** · 08-13-2007, 11:06 AM

Reworked the code in a different way to look at the problem but it was not working, I was wondering if someone would be kind enough to pick out the mistakes. Basically the could is a "solver" problem where given an specific IRR and an input column from the user, MinP, I would get the other variable. The following is not working though:
[/LIST]substitute values for variables in solver formula and [/LIST]the offset of MinP variable

I would appreciate if somebody could help me. Thanks

Sub GoalSeek()

Dim IRR, MinP, Num, Row

Set IRR = Application.InputBox( _
prompt:="Select IRR Cell", Type:=8)

Set MinP = Application.InputBox( _
prompt:="Select mininum lead price", Type:=8)

Row = 0

Do While Row < 1

SolverReset
SolverOk SetCell:="$F$8", MaxMinVal:=3, ValueOf:=IRR, ByChange:="$F$6:$F$7"
SolverAdd CellRef:="$F$6", Relation:=2, FormulaText:=MinP
SolverOk SetCell:="$F$8", MaxMinVal:=3, ValueOf:=IRR, ByChange:="$F$6:$F$7"
SolverSolve

ActiveCell.Formula = Range("F7")

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

MinP.Offset(rowOffset:=1)

Row = Row + 1

Loop

End Sub

**mjsheen** · 06-12-2009, 02:42 AM

I think it will exit your loop in the first sequence because Row is not smaller than 1.

Do While Row < 1
Row = Row + 1

**Phil_V** · 06-12-2009, 04:13 AM

As above, your loop will not loop, but will just execute the code once, but perhaps this is what you intended.

Mainly I would point back at post #2:

I have renamed your IRR variable to NewIRR - there is already a VBA instruction called IRR, and this will cause havoc with your code, throwing errors up and all sorts

Until you have implemented that in YOUR code as well it would seem that any bug chasing is going to be futile.