Goal Seek Challenge

[ChrisHockley]

Hello Excel experts... boy could I use your help.

I have a 14,000+ row Excel document that i need to do a standard goal seek in one column... down the entire document. I can't possibly have to hit CTRL+w in a macro fourteen thousand times?!?

Is there a way around this that could work?

I accidentally recorded a macro in another document yesterday and forgot to hit stop after doing my first goal seek. Then stopped it. Used it and it actually would do the goal seek down a column until it hit an empty row. It would have a run error and from there i could hit okay and start it again. For the life of me I can't get it to do this again.

Any help would be so greatly appreciated.

Thank you.

 

[Logit]

.
How to Use VBA to Loop goal seek down columns

 

[ChrisHockley]

Sorry this seems to be far beyond my ability to complete.

So in my specific instance I need column 'BD' to be .065 by changing 'M'. In this case from rows 1324 down to 14541.

I recorded a Macro of doing a simple goal seek twice which is great in that it allows me to do two at a time. But i don't know how to alter the details pasted below to say keep doing this until the end of the doc (to row 14541) and to not end up with a runtime error if column BC (column that BD is pulling from) is empty.

Can you help me further with this information?


Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+w
'
ActiveCell.Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.GoalSeek Goal:=0.065, ChangingCell:=ActiveCell.Offset(0, -43). _
Range("A1")
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.GoalSeek Goal:=0.065, ChangingCell:=ActiveCell.Offset(0, -43). _
Range("A1")
End Sub

 

[AJG85]

I was using goal seek formula for calculating implied volatility in black 76 model. However, when setting a cell to a target value was not yielding the target value and the implied volatility results were absurd.

Futures contract price

Strike Price

Column D Implied volatility of underlying

Risk free interest rate

Current Date

Maturity Date of options contract

Days to expiry days to expiry

x

y (Adjusted Return)

z (Time adjusted volatility)

D1 (Delta 1)

D2 (Delta 2)

N1b

N2

e^-rT

Column V Market Price C (Call Premium)

Market Price P (Put Premium)

Call Contract Delta

Put Contract Delta

Target cells in column V to change cells in column D

F

K

v

r

dt

t

T

((v^2)/2)*T

v*(SQRT(T))

(x+y)/z

D1 - z

NORMSDIST(D1)

NORMSDIST(D2)

(e^-rT)*[F*N1-K*N2]

C-[(e^-rT)*(F-K)]

N1*e^-rT

(N1-1)*e^-rT

4514​

3000​

61.06%​

0.0368​

02-04-25​

16-04-25​

0.038356164​

0.40857139​

0.00715021​

0.119584361​

3.476387694​

3.356803333​

0.999745891​

0.999605754​

0.998589489​

1511.9​

0.04​

0.998335739​

-0.00025375​

1511.9​

Set cell

V4

4514​

3050​

##########​

0.0368​

02-04-25​

16-04-25​

0.038356164​

0.392042088​

3.70938E+19​

8613219613​

4306609806​

-4306609806​

1​

0​

0.998589489​

4507.63​

3045.69​

0.998589489​

0​

1461.9​

T value

1461.9​

By changing cell

D4

Cell V4 does yield 1461.9 but gives a absurd number

 

[johnnyL]

@ChrisHockley I think @Logit was suggesting looking at something like the following:

I have never used this approach, but you can test this, if you like:

Sub Macro3()
    Dim i   As Long
'
    With ActiveSheet
        For i = 1324 To 14541
            .Range("BD" & i).GoalSeek Goal:=0.065, ChangingCell:=.Range("M" & i)
        Next i
    End With
End Sub

 

[AJG85]

@ChrisHockley I think @Logit was suggesting looking at something like the following:

I have never used this approach, but you can test this, if you like:

Sub Macro3()
    Dim i   As Long
'
    With ActiveSheet
        For i = 1324 To 14541
            .Range("BD" & i).GoalSeek Goal:=0.065, ChangingCell:=.Range("M" & i)
        Next i
    End With
End Sub

Used this VBA but answer to the second row where market price is to be set at 1461.9 is still coming the same