Goal Seek Macro

D

Donald

Board Regular
Joined
Apr 15, 2002
Messages
138
Is there a way to replace =0.21 with an actual cell in in excel (say "a7"), so that I don't have to go into the script to change that part of the macro every time I want to change my factor to 0.21, 0.22, 0.23, etc, etc...?

Sub IRR_Macro()
'
' IRR_Macro Macro
' Macro recorded 3/13/2008 by Donald
'

'
Range("G7").Select
Range("G7").GoalSeek Goal:=0.21, ChangingCell:=Range("F7")
Range("G8").Select
Range("G8").GoalSeek Goal:=0.21, ChangingCell:=Range("F8")
Range("G9").Select
Range("G9").GoalSeek Goal:=0.21, ChangingCell:=Range("F9")
Range("J7").Select
Range("J7").GoalSeek Goal:=0.21, ChangingCell:=Range("I7")
Range("J8").GoalSeek Goal:=0.21, ChangingCell:=Range("I8")
Range("J9").Select
Range("J9").GoalSeek Goal:=0.21, ChangingCell:=Range("I9")
Range("M7").Select
Range("M7").GoalSeek Goal:=0.21, ChangingCell:=Range("L7")
Range("M8").Select
Range("M8").GoalSeek Goal:=0.21, ChangingCell:=Range("L8")
Range("M9").Select
Range("M9").GoalSeek Goal:=0.21, ChangingCell:=Range("L9")
Range("P7").Select
Range("P7").GoalSeek Goal:=0.21, ChangingCell:=Range("O7")
Range("P8").Select
Range("P8").GoalSeek Goal:=0.21, ChangingCell:=Range("O8")
Range("P9").Select
Range("P9").GoalSeek Goal:=0.21, ChangingCell:=Range("O9")
Range("S7").Select
Range("S7").GoalSeek Goal:=0.21, ChangingCell:=Range("R7")
Range("S8").Select
Range("S8").GoalSeek Goal:=0.21, ChangingCell:=Range("R8")
Range("S9").Select
Range("S9").GoalSeek Goal:=0.21, ChangingCell:=Range("R9")
Range("V7").Select
Range("V7").GoalSeek Goal:=0.21, ChangingCell:=Range("U7")
Range("V8").Select
Range("V8").GoalSeek Goal:=0.21, ChangingCell:=Range("U8")
Range("V9").Select
Range("V9").GoalSeek Goal:=0.21, ChangingCell:=Range("U9")
Range("Y7").Select
Range("Y7").GoalSeek Goal:=0.21, ChangingCell:=Range("X7")
Range("Y8").Select
Range("Y8").GoalSeek Goal:=0.21, ChangingCell:=Range("X8")
Range("Y9").Select
Range("Y9").GoalSeek Goal:=0.21, ChangingCell:=Range("X9")
Range("AB7").Select
Range("AB7").GoalSeek Goal:=0.21, ChangingCell:=Range("AA7")
Range("AB8").Select
Range("AB8").GoalSeek Goal:=0.21, ChangingCell:=Range("AA8")
Range("AB9").Select
Range("AB9").GoalSeek Goal:=0.21, ChangingCell:=Range("AA9")
Range("G22").Select
Range("G22").GoalSeek Goal:=0.21, ChangingCell:=Range("F22")
Range("G23").Select
Range("G23").GoalSeek Goal:=0.21, ChangingCell:=Range("F23")
Range("G24").Select
Range("G24").GoalSeek Goal:=0.21, ChangingCell:=Range("F24")
Range("G25").Select
Range("G25").GoalSeek Goal:=0.21, ChangingCell:=Range("F25")
Range("G26").Select
Range("G26").GoalSeek Goal:=0.21, ChangingCell:=Range("F26")
Range("G27").Select
Range("G27").GoalSeek Goal:=0.21, ChangingCell:=Range("F27")
Range("G28").Select
Range("G28").GoalSeek Goal:=0.21, ChangingCell:=Range("F28")
Range("G29").Select
Range("G29").GoalSeek Goal:=0.21, ChangingCell:=Range("F29")
Range("J22").Select
Range("J22").GoalSeek Goal:=0.21, ChangingCell:=Range("I22")
Range("J23").Select
Range("J23").GoalSeek Goal:=0.21, ChangingCell:=Range("I23")
Range("J24").Select
Range("J24").GoalSeek Goal:=0.21, ChangingCell:=Range("I24")
Range("J25").Select
Range("J25").GoalSeek Goal:=0.21, ChangingCell:=Range("I25")
Range("J26").Select
Range("J26").GoalSeek Goal:=0.21, ChangingCell:=Range("I26")
Range("J27").Select
Range("J27").GoalSeek Goal:=0.21, ChangingCell:=Range("I27")
Range("J28").Select
Range("J28").GoalSeek Goal:=0.21, ChangingCell:=Range("I28")
Range("J29").Select
Range("J29").GoalSeek Goal:=0.21, ChangingCell:=Range("I29")
Range("M22").Select
Range("M22").GoalSeek Goal:=0.21, ChangingCell:=Range("L22")
Range("M23").Select
Range("M23").GoalSeek Goal:=0.21, ChangingCell:=Range("L23")
Range("M24").Select
Range("M24").GoalSeek Goal:=0.21, ChangingCell:=Range("L24")
Range("M25").Select
Range("M25").GoalSeek Goal:=0.21, ChangingCell:=Range("L25")
Range("M26").Select
Range("M26").GoalSeek Goal:=0.21, ChangingCell:=Range("L26")
Range("M27").Select
Range("M27").GoalSeek Goal:=0.21, ChangingCell:=Range("L27")
Range("M28").Select
Range("M28").GoalSeek Goal:=0.21, ChangingCell:=Range("L28")
Range("M29").Select
Range("M29").GoalSeek Goal:=0.21, ChangingCell:=Range("L29")
Range("P22").Select
Range("P22").GoalSeek Goal:=0.21, ChangingCell:=Range("O22")
Range("P23").Select
Range("P23").GoalSeek Goal:=0.21, ChangingCell:=Range("O23")
Range("P24").Select
Range("P24").GoalSeek Goal:=0.21, ChangingCell:=Range("O24")
Range("P25").Select
Range("P25").GoalSeek Goal:=0.21, ChangingCell:=Range("O25")
Range("P26").Select
Range("P26").GoalSeek Goal:=0.21, ChangingCell:=Range("O26")
Range("P27").Select
Range("P27").GoalSeek Goal:=0.21, ChangingCell:=Range("O27")
Range("P28").Select
Range("P28").GoalSeek Goal:=0.21, ChangingCell:=Range("O28")
Range("P29").Select
Range("P29").GoalSeek Goal:=0.21, ChangingCell:=Range("O29")
Range("S22").Select
Range("S22").GoalSeek Goal:=0.21, ChangingCell:=Range("R22")
Range("S23").Select
Range("S23").GoalSeek Goal:=0.21, ChangingCell:=Range("R23")
Range("S24").Select
Range("S24").GoalSeek Goal:=0.21, ChangingCell:=Range("R24")
Range("S25").Select
Range("S25").GoalSeek Goal:=0.21, ChangingCell:=Range("R25")
Range("S26").Select
Range("S26").GoalSeek Goal:=0.21, ChangingCell:=Range("R26")
Range("S27").Select
Range("S27").GoalSeek Goal:=0.21, ChangingCell:=Range("R27")
Range("S28").Select
Range("S28").GoalSeek Goal:=0.21, ChangingCell:=Range("R28")
Range("S29").Select
Range("S29").GoalSeek Goal:=0.21, ChangingCell:=Range("R29")
Range("V22").Select
Range("V22").GoalSeek Goal:=0.21, ChangingCell:=Range("U22")
Range("V23").Select
Range("V23").GoalSeek Goal:=0.21, ChangingCell:=Range("U23")
Range("V24").Select
Range("V24").GoalSeek Goal:=0.21, ChangingCell:=Range("U24")
Range("V25").Select
Range("V25").GoalSeek Goal:=0.21, ChangingCell:=Range("U25")
Range("V26").Select
Range("V26").GoalSeek Goal:=0.21, ChangingCell:=Range("U26")
Range("V27").Select
Range("V27").GoalSeek Goal:=0.21, ChangingCell:=Range("U27")
Range("V28").Select
Range("V28").GoalSeek Goal:=0.21, ChangingCell:=Range("U28")
Range("V29").Select
Range("V29").GoalSeek Goal:=0.21, ChangingCell:=Range("U29")
Range("Y22").Select
Range("Y22").GoalSeek Goal:=0.21, ChangingCell:=Range("X22")
Range("Y23").Select
Range("Y23").GoalSeek Goal:=0.21, ChangingCell:=Range("X23")
Range("Y24").Select
Range("Y24").GoalSeek Goal:=0.21, ChangingCell:=Range("X24")
Range("Y25").Select
Range("Y25").GoalSeek Goal:=0.21, ChangingCell:=Range("X25")
Range("Y26").Select
Range("Y26").GoalSeek Goal:=0.21, ChangingCell:=Range("X26")
Range("Y27").Select
Range("Y27").GoalSeek Goal:=0.21, ChangingCell:=Range("X27")
Range("Y28").Select
Range("Y28").GoalSeek Goal:=0.21, ChangingCell:=Range("X28")
Range("Y29").Select
Range("Y29").GoalSeek Goal:=0.21, ChangingCell:=Range("X29")
Range("AB22").Select
Range("AB22").GoalSeek Goal:=0.21, ChangingCell:=Range("AA22")
Range("AB23").Select
Range("AB23").GoalSeek Goal:=0.21, ChangingCell:=Range("AA23")
Range("AB24").Select
Range("AB24").GoalSeek Goal:=0.21, ChangingCell:=Range("AA24")
Range("AB25").Select
Range("AB25").GoalSeek Goal:=0.21, ChangingCell:=Range("AA25")
Range("AB26").Select
Range("AB26").GoalSeek Goal:=0.21, ChangingCell:=Range("AA26")
Range("AB27").Select
Range("AB27").GoalSeek Goal:=0.21, ChangingCell:=Range("AA27")
Range("AB28").Select
Range("AB28").GoalSeek Goal:=0.21, ChangingCell:=Range("AA28")
Range("AB29").Select
Range("AB29").GoalSeek Goal:=0.21, ChangingCell:=Range("AA29")
Range("G40").Select
Range("G40").GoalSeek Goal:=0.21, ChangingCell:=Range("F40")
Range("G41").Select
Range("G41").GoalSeek Goal:=0.21, ChangingCell:=Range("F41")
Range("G42").Select
Range("G42").GoalSeek Goal:=0.21, ChangingCell:=Range("F42")
Range("G43").Select
Range("G43").GoalSeek Goal:=0.21, ChangingCell:=Range("F43")
Range("G44").Select
Range("G44").GoalSeek Goal:=0.21, ChangingCell:=Range("F44")
Range("G45").Select
Range("G45").GoalSeek Goal:=0.21, ChangingCell:=Range("F45")
Range("G46").Select
Range("G46").GoalSeek Goal:=0.21, ChangingCell:=Range("F46")
Range("J40").Select
Range("J40").GoalSeek Goal:=0.21, ChangingCell:=Range("I40")
Range("J41").Select
Range("J41").GoalSeek Goal:=0.21, ChangingCell:=Range("I41")
Range("J42").Select
Range("J42").GoalSeek Goal:=0.21, ChangingCell:=Range("I42")
Range("J43").Select
Range("J43").GoalSeek Goal:=0.21, ChangingCell:=Range("I43")
Range("J44").Select
Range("J44").GoalSeek Goal:=0.21, ChangingCell:=Range("I44")
Range("J45").Select
Range("J45").GoalSeek Goal:=0.21, ChangingCell:=Range("I45")
Range("J46").Select
Range("J46").GoalSeek Goal:=0.21, ChangingCell:=Range("I46")
Range("M40").Select
Range("M40").GoalSeek Goal:=0.21, ChangingCell:=Range("L40")
Range("M41").Select
Range("M41").GoalSeek Goal:=0.21, ChangingCell:=Range("L41")
Range("M42").Select
Range("M42").GoalSeek Goal:=0.21, ChangingCell:=Range("L42")
Range("M43").Select
Range("M43").GoalSeek Goal:=0.21, ChangingCell:=Range("L43")
Range("M44").Select
Range("M44").GoalSeek Goal:=0.21, ChangingCell:=Range("L44")
Range("M45").Select
Range("M45").GoalSeek Goal:=0.21, ChangingCell:=Range("L45")
Range("M46").Select
Range("M46").GoalSeek Goal:=0.21, ChangingCell:=Range("L46")
Range("P40").Select
Range("P40").GoalSeek Goal:=0.21, ChangingCell:=Range("O40")
Range("P41").Select
Range("P41").GoalSeek Goal:=0.21, ChangingCell:=Range("O41")
Range("P42").Select
Range("P42").GoalSeek Goal:=0.21, ChangingCell:=Range("O42")
Range("P43").Select
Range("P43").GoalSeek Goal:=0.21, ChangingCell:=Range("O43")
Range("P44").Select
Range("P44").GoalSeek Goal:=0.21, ChangingCell:=Range("O44")
Range("P45").Select
Range("P45").GoalSeek Goal:=0.21, ChangingCell:=Range("O45")
Range("P46").Select
Range("P46").GoalSeek Goal:=0.21, ChangingCell:=Range("O46")
Range("S40").Select
Range("S40").GoalSeek Goal:=0.21, ChangingCell:=Range("R40")
Range("S41").Select
Range("S41").GoalSeek Goal:=0.21, ChangingCell:=Range("R41")
Range("S42").Select
Range("S42").GoalSeek Goal:=0.21, ChangingCell:=Range("R42")
Range("S43").Select
Range("S43").GoalSeek Goal:=0.21, ChangingCell:=Range("R43")
Range("S44").Select
Range("S44").GoalSeek Goal:=0.21, ChangingCell:=Range("R44")
Range("S45").Select
Range("S45").GoalSeek Goal:=0.21, ChangingCell:=Range("R45")
Range("S46").Select
Range("S46").GoalSeek Goal:=0.21, ChangingCell:=Range("R46")
Range("V40").Select
Range("V40").GoalSeek Goal:=0.21, ChangingCell:=Range("U40")
Range("V41").Select
Range("V41").GoalSeek Goal:=0.21, ChangingCell:=Range("U41")
Range("V42").Select
Range("V42").GoalSeek Goal:=0.21, ChangingCell:=Range("U42")
Range("V43").Select
Range("V43").GoalSeek Goal:=0.21, ChangingCell:=Range("U43")
Range("V44").Select
Range("V44").GoalSeek Goal:=0.21, ChangingCell:=Range("U44")
Range("V45").Select
Range("V45").GoalSeek Goal:=0.21, ChangingCell:=Range("U45")
Range("V46").Select
Range("V46").GoalSeek Goal:=0.21, ChangingCell:=Range("U46")
Range("Y40").Select
Range("Y40").GoalSeek Goal:=0.21, ChangingCell:=Range("X40")
Range("Y41").Select
Range("Y41").GoalSeek Goal:=0.21, ChangingCell:=Range("X41")
Range("Y42").Select
Range("Y42").GoalSeek Goal:=0.21, ChangingCell:=Range("X42")
Range("Y43").Select
Range("Y43").GoalSeek Goal:=0.21, ChangingCell:=Range("X43")
Range("Y44").Select
Range("Y44").GoalSeek Goal:=0.21, ChangingCell:=Range("X44")
Range("Y45").Select
Range("Y45").GoalSeek Goal:=0.21, ChangingCell:=Range("X45")
Range("Y46").Select
Range("Y46").GoalSeek Goal:=0.21, ChangingCell:=Range("X46")
Range("AB40").Select
Range("AB40").GoalSeek Goal:=0.21, ChangingCell:=Range("AA40")
Range("AB41").Select
Range("AB41").GoalSeek Goal:=0.21, ChangingCell:=Range("AA41")
Range("AB42").Select
Range("AB42").GoalSeek Goal:=0.21, ChangingCell:=Range("AA42")
Range("AB43").Select
Range("AB43").GoalSeek Goal:=0.21, ChangingCell:=Range("AA43")
Range("AB44").Select
Range("AB44").GoalSeek Goal:=0.21, ChangingCell:=Range("AA44")
Range("AB45").Select
Range("AB45").GoalSeek Goal:=0.21, ChangingCell:=Range("AA45")
Range("AB46").Select
Range("AB46").GoalSeek Goal:=0.21, ChangingCell:=Range("AA46")
Range("G65").Select
Range("G65").GoalSeek Goal:=0.21, ChangingCell:=Range("F65")
Range("G66").Select
Range("G66").GoalSeek Goal:=0.21, ChangingCell:=Range("F66")
Range("G67").Select
Range("G67").GoalSeek Goal:=0.21, ChangingCell:=Range("F67")
Range("G68").Select
Range("G68").GoalSeek Goal:=0.21, ChangingCell:=Range("F68")
Range("G69").Select
Range("G69").GoalSeek Goal:=0.21, ChangingCell:=Range("F69")
Range("G70").Select
Range("G70").GoalSeek Goal:=0.21, ChangingCell:=Range("F70")
Range("G71").Select
Range("G71").GoalSeek Goal:=0.21, ChangingCell:=Range("F71")
Range("G72").Select
Range("G72").GoalSeek Goal:=0.21, ChangingCell:=Range("F72")
Range("G73").Select
Range("G73").GoalSeek Goal:=0.21, ChangingCell:=Range("F73")
Range("G74").Select
Range("G74").GoalSeek Goal:=0.21, ChangingCell:=Range("F74")
Range("G75").Select
Range("G75").GoalSeek Goal:=0.21, ChangingCell:=Range("F75")
Range("G76").Select
Range("G76").GoalSeek Goal:=0.21, ChangingCell:=Range("F76")
Range("G77").Select
Range("G77").GoalSeek Goal:=0.21, ChangingCell:=Range("F77")
Range("G78").Select
Range("G78").GoalSeek Goal:=0.21, ChangingCell:=Range("F78")
Range("G79").Select
Range("G79").GoalSeek Goal:=0.21, ChangingCell:=Range("F79")
Range("j65").Select
Range("j65").GoalSeek Goal:=0.21, ChangingCell:=Range("i65")
Range("j66").Select
Range("j66").GoalSeek Goal:=0.21, ChangingCell:=Range("i66")
Range("j67").Select
Range("j67").GoalSeek Goal:=0.21, ChangingCell:=Range("i67")
Range("j68").Select
Range("j68").GoalSeek Goal:=0.21, ChangingCell:=Range("i68")
Range("j69").Select
Range("j69").GoalSeek Goal:=0.21, ChangingCell:=Range("i69")
Range("j70").Select
Range("j70").GoalSeek Goal:=0.21, ChangingCell:=Range("i70")
Range("j71").Select
Range("j71").GoalSeek Goal:=0.21, ChangingCell:=Range("i71")
Range("j72").Select
Range("j72").GoalSeek Goal:=0.21, ChangingCell:=Range("i72")
Range("j73").Select
Range("j73").GoalSeek Goal:=0.21, ChangingCell:=Range("i73")
Range("j74").Select
Range("j74").GoalSeek Goal:=0.21, ChangingCell:=Range("i74")
Range("j75").Select
Range("j75").GoalSeek Goal:=0.21, ChangingCell:=Range("i75")
Range("j76").Select
Range("j76").GoalSeek Goal:=0.21, ChangingCell:=Range("i76")
Range("j77").Select
Range("j77").GoalSeek Goal:=0.21, ChangingCell:=Range("i77")
Range("j78").Select
Range("j78").GoalSeek Goal:=0.21, ChangingCell:=Range("i78")
Range("j79").Select
Range("j79").GoalSeek Goal:=0.21, ChangingCell:=Range("i79")
Range("m65").Select
Range("m65").GoalSeek Goal:=0.21, ChangingCell:=Range("l65")
Range("m66").Select
Range("m66").GoalSeek Goal:=0.21, ChangingCell:=Range("l66")
Range("m67").Select
Range("m67").GoalSeek Goal:=0.21, ChangingCell:=Range("l67")
Range("m68").Select
Range("m68").GoalSeek Goal:=0.21, ChangingCell:=Range("l68")
Range("m69").Select
Range("m69").GoalSeek Goal:=0.21, ChangingCell:=Range("l69")
Range("m70").Select
Range("m70").GoalSeek Goal:=0.21, ChangingCell:=Range("l70")
Range("m71").Select
Range("m71").GoalSeek Goal:=0.21, ChangingCell:=Range("l71")
Range("m72").Select
Range("m72").GoalSeek Goal:=0.21, ChangingCell:=Range("l72")
Range("m73").Select
Range("m73").GoalSeek Goal:=0.21, ChangingCell:=Range("l73")
Range("m74").Select
Range("m74").GoalSeek Goal:=0.21, ChangingCell:=Range("l74")
Range("m75").Select
Range("m75").GoalSeek Goal:=0.21, ChangingCell:=Range("l75")
Range("m76").Select
Range("m76").GoalSeek Goal:=0.21, ChangingCell:=Range("l76")
Range("m77").Select
Range("m77").GoalSeek Goal:=0.21, ChangingCell:=Range("l77")
Range("m78").Select
Range("m78").GoalSeek Goal:=0.21, ChangingCell:=Range("l78")
Range("m79").Select
Range("m79").GoalSeek Goal:=0.21, ChangingCell:=Range("l79")
Range("p65").Select
Range("p65").GoalSeek Goal:=0.21, ChangingCell:=Range("o65")
Range("p66").Select
Range("p66").GoalSeek Goal:=0.21, ChangingCell:=Range("o66")
Range("p67").Select
Range("p67").GoalSeek Goal:=0.21, ChangingCell:=Range("o67")
Range("p68").Select
Range("p68").GoalSeek Goal:=0.21, ChangingCell:=Range("o68")
Range("p69").Select
Range("p69").GoalSeek Goal:=0.21, ChangingCell:=Range("o69")
Range("p70").Select
Range("p70").GoalSeek Goal:=0.21, ChangingCell:=Range("o70")
Range("p71").Select
Range("p71").GoalSeek Goal:=0.21, ChangingCell:=Range("o71")
Range("p72").Select
Range("p72").GoalSeek Goal:=0.21, ChangingCell:=Range("o72")
Range("p73").Select
Range("p73").GoalSeek Goal:=0.21, ChangingCell:=Range("o73")
Range("p74").Select
Range("p74").GoalSeek Goal:=0.21, ChangingCell:=Range("o74")
Range("p75").Select
Range("p75").GoalSeek Goal:=0.21, ChangingCell:=Range("o75")
Range("p76").Select
Range("p76").GoalSeek Goal:=0.21, ChangingCell:=Range("o76")
Range("p77").Select
Range("p77").GoalSeek Goal:=0.21, ChangingCell:=Range("o77")
Range("p78").Select
Range("p78").GoalSeek Goal:=0.21, ChangingCell:=Range("o78")
Range("p79").Select
Range("p79").GoalSeek Goal:=0.21, ChangingCell:=Range("o79")
Range("s65").Select
Range("s65").GoalSeek Goal:=0.21, ChangingCell:=Range("r65")
Range("s66").Select
Range("s66").GoalSeek Goal:=0.21, ChangingCell:=Range("r66")
Range("s67").Select
Range("s67").GoalSeek Goal:=0.21, ChangingCell:=Range("r67")
Range("s68").Select
Range("s68").GoalSeek Goal:=0.21, ChangingCell:=Range("r68")
Range("s69").Select
Range("s69").GoalSeek Goal:=0.21, ChangingCell:=Range("r69")
Range("s70").Select
Range("s70").GoalSeek Goal:=0.21, ChangingCell:=Range("r70")
Range("s71").Select
Range("s71").GoalSeek Goal:=0.21, ChangingCell:=Range("r71")
Range("s72").Select
Range("s72").GoalSeek Goal:=0.21, ChangingCell:=Range("r72")
Range("s73").Select
Range("s73").GoalSeek Goal:=0.21, ChangingCell:=Range("r73")
Range("s74").Select
Range("s74").GoalSeek Goal:=0.21, ChangingCell:=Range("r74")
Range("s75").Select
Range("s75").GoalSeek Goal:=0.21, ChangingCell:=Range("r75")
Range("s76").Select
Range("s76").GoalSeek Goal:=0.21, ChangingCell:=Range("r76")
Range("s77").Select
Range("s77").GoalSeek Goal:=0.21, ChangingCell:=Range("r77")
Range("s78").Select
Range("s78").GoalSeek Goal:=0.21, ChangingCell:=Range("r78")
Range("s79").Select
Range("s79").GoalSeek Goal:=0.21, ChangingCell:=Range("r79")
Range("v65").Select
Range("v65").GoalSeek Goal:=0.21, ChangingCell:=Range("u65")
Range("v66").Select
Range("v66").GoalSeek Goal:=0.21, ChangingCell:=Range("u66")
Range("v67").Select
Range("v67").GoalSeek Goal:=0.21, ChangingCell:=Range("u67")
Range("v68").Select
Range("v68").GoalSeek Goal:=0.21, ChangingCell:=Range("u68")
Range("v69").Select
Range("v69").GoalSeek Goal:=0.21, ChangingCell:=Range("u69")
Range("v70").Select
Range("v70").GoalSeek Goal:=0.21, ChangingCell:=Range("u70")
Range("v71").Select
Range("v71").GoalSeek Goal:=0.21, ChangingCell:=Range("u71")
Range("v72").Select
Range("v72").GoalSeek Goal:=0.21, ChangingCell:=Range("u72")
Range("v73").Select
Range("v73").GoalSeek Goal:=0.21, ChangingCell:=Range("u73")
Range("v74").Select
Range("v74").GoalSeek Goal:=0.21, ChangingCell:=Range("u74")
Range("v75").Select
Range("v75").GoalSeek Goal:=0.21, ChangingCell:=Range("u75")
Range("v76").Select
Range("v76").GoalSeek Goal:=0.21, ChangingCell:=Range("u76")
Range("v77").Select
Range("v77").GoalSeek Goal:=0.21, ChangingCell:=Range("u77")
Range("v78").Select
Range("v78").GoalSeek Goal:=0.21, ChangingCell:=Range("u78")
Range("v79").Select
Range("v79").GoalSeek Goal:=0.21, ChangingCell:=Range("u79")
Range("y65").Select
Range("y65").GoalSeek Goal:=0.21, ChangingCell:=Range("x65")
Range("y66").Select
Range("y66").GoalSeek Goal:=0.21, ChangingCell:=Range("x66")
Range("y67").Select
Range("y67").GoalSeek Goal:=0.21, ChangingCell:=Range("x67")
Range("y68").Select
Range("y68").GoalSeek Goal:=0.21, ChangingCell:=Range("x68")
Range("y69").Select
Range("y69").GoalSeek Goal:=0.21, ChangingCell:=Range("x69")
Range("y70").Select
Range("y70").GoalSeek Goal:=0.21, ChangingCell:=Range("x70")
Range("y71").Select
Range("y71").GoalSeek Goal:=0.21, ChangingCell:=Range("x71")
Range("y72").Select
Range("y72").GoalSeek Goal:=0.21, ChangingCell:=Range("x72")
Range("y73").Select
Range("y73").GoalSeek Goal:=0.21, ChangingCell:=Range("x73")
Range("y74").Select
Range("y74").GoalSeek Goal:=0.21, ChangingCell:=Range("x74")
Range("y75").Select
Range("y75").GoalSeek Goal:=0.21, ChangingCell:=Range("x75")
Range("y76").Select
Range("y76").GoalSeek Goal:=0.21, ChangingCell:=Range("x76")
Range("y77").Select
Range("y77").GoalSeek Goal:=0.21, ChangingCell:=Range("x77")
Range("y78").Select
Range("y78").GoalSeek Goal:=0.21, ChangingCell:=Range("x78")
Range("y79").Select
Range("y79").GoalSeek Goal:=0.21, ChangingCell:=Range("x79")
Range("ab65").Select
Range("ab65").GoalSeek Goal:=0.21, ChangingCell:=Range("aa65")
Range("ab66").Select
Range("ab66").GoalSeek Goal:=0.21, ChangingCell:=Range("aa66")
Range("ab67").Select
Range("ab67").GoalSeek Goal:=0.21, ChangingCell:=Range("aa67")
Range("ab68").Select
Range("ab68").GoalSeek Goal:=0.21, ChangingCell:=Range("aa68")
Range("ab69").Select
Range("ab69").GoalSeek Goal:=0.21, ChangingCell:=Range("aa69")
Range("ab70").Select
Range("ab70").GoalSeek Goal:=0.21, ChangingCell:=Range("aa70")
Range("ab71").Select
Range("ab71").GoalSeek Goal:=0.21, ChangingCell:=Range("aa71")
Range("ab72").Select
Range("ab72").GoalSeek Goal:=0.21, ChangingCell:=Range("aa72")
Range("ab73").Select
Range("ab73").GoalSeek Goal:=0.21, ChangingCell:=Range("aa73")
Range("ab74").Select
Range("ab74").GoalSeek Goal:=0.21, ChangingCell:=Range("aa74")
Range("ab75").Select
Range("ab75").GoalSeek Goal:=0.21, ChangingCell:=Range("aa75")
Range("ab76").Select
Range("ab76").GoalSeek Goal:=0.21, ChangingCell:=Range("aa76")
Range("ab77").Select
Range("ab77").GoalSeek Goal:=0.21, ChangingCell:=Range("aa77")
Range("ab78").Select
Range("ab78").GoalSeek Goal:=0.21, ChangingCell:=Range("aa78")
Range("ab79").Select
Range("ab79").GoalSeek Goal:=0.21, ChangingCell:=Range("aa79")
Range("g89").Select
Range("g89").GoalSeek Goal:=0.21, ChangingCell:=Range("f89")
Range("g90").Select
Range("g90").GoalSeek Goal:=0.21, ChangingCell:=Range("f90")
Range("g91").Select
Range("g91").GoalSeek Goal:=0.21, ChangingCell:=Range("f91")
Range("g92").Select
Range("g92").GoalSeek Goal:=0.21, ChangingCell:=Range("f92")
Range("g93").Select
Range("g93").GoalSeek Goal:=0.21, ChangingCell:=Range("f93")
Range("g94").Select
Range("g94").GoalSeek Goal:=0.21, ChangingCell:=Range("f94")
Range("j89").Select
Range("j89").GoalSeek Goal:=0.21, ChangingCell:=Range("i89")
Range("j90").Select
Range("j90").GoalSeek Goal:=0.21, ChangingCell:=Range("i90")
Range("j91").Select
Range("j91").GoalSeek Goal:=0.21, ChangingCell:=Range("i91")
Range("j92").Select
Range("j92").GoalSeek Goal:=0.21, ChangingCell:=Range("i92")
Range("j93").Select
Range("j93").GoalSeek Goal:=0.21, ChangingCell:=Range("i93")
Range("j94").Select
Range("j94").GoalSeek Goal:=0.21, ChangingCell:=Range("i94")
Range("m89").Select
Range("m89").GoalSeek Goal:=0.21, ChangingCell:=Range("l89")
Range("m90").Select
Range("m90").GoalSeek Goal:=0.21, ChangingCell:=Range("l90")
Range("m91").Select
Range("m91").GoalSeek Goal:=0.21, ChangingCell:=Range("l91")
Range("m92").Select
Range("m92").GoalSeek Goal:=0.21, ChangingCell:=Range("l92")
Range("m93").Select
Range("m93").GoalSeek Goal:=0.21, ChangingCell:=Range("l93")
Range("m94").Select
Range("m94").GoalSeek Goal:=0.21, ChangingCell:=Range("l94")
Range("p89").Select
Range("p89").GoalSeek Goal:=0.21, ChangingCell:=Range("o89")
Range("p90").Select
Range("p90").GoalSeek Goal:=0.21, ChangingCell:=Range("o90")
Range("p91").Select
Range("p91").GoalSeek Goal:=0.21, ChangingCell:=Range("o91")
Range("p92").Select
Range("p92").GoalSeek Goal:=0.21, ChangingCell:=Range("o92")
Range("p93").Select
Range("p93").GoalSeek Goal:=0.21, ChangingCell:=Range("o93")
Range("p94").Select
Range("p94").GoalSeek Goal:=0.21, ChangingCell:=Range("o94")
Range("s89").Select
Range("s89").GoalSeek Goal:=0.21, ChangingCell:=Range("r89")
Range("s90").Select
Range("s90").GoalSeek Goal:=0.21, ChangingCell:=Range("r90")
Range("s91").Select
Range("s91").GoalSeek Goal:=0.21, ChangingCell:=Range("r91")
Range("s92").Select
Range("s92").GoalSeek Goal:=0.21, ChangingCell:=Range("r92")
Range("s93").Select
Range("s93").GoalSeek Goal:=0.21, ChangingCell:=Range("r93")
Range("s94").Select
Range("s94").GoalSeek Goal:=0.21, ChangingCell:=Range("r94")
Range("v89").Select
Range("v89").GoalSeek Goal:=0.21, ChangingCell:=Range("u89")
Range("v90").Select
Range("v90").GoalSeek Goal:=0.21, ChangingCell:=Range("u90")
Range("v91").Select
Range("v91").GoalSeek Goal:=0.21, ChangingCell:=Range("u91")
Range("v92").Select
Range("v92").GoalSeek Goal:=0.21, ChangingCell:=Range("u92")
Range("v93").Select
Range("v93").GoalSeek Goal:=0.21, ChangingCell:=Range("u93")
Range("v94").Select
Range("v94").GoalSeek Goal:=0.21, ChangingCell:=Range("u94")
Range("y89").Select
Range("y89").GoalSeek Goal:=0.21, ChangingCell:=Range("x89")
Range("y90").Select
Range("y90").GoalSeek Goal:=0.21, ChangingCell:=Range("x90")
Range("y91").Select
Range("y91").GoalSeek Goal:=0.21, ChangingCell:=Range("x91")
Range("y92").Select
Range("y92").GoalSeek Goal:=0.21, ChangingCell:=Range("x92")
Range("y93").Select
Range("y93").GoalSeek Goal:=0.21, ChangingCell:=Range("x93")
Range("y94").Select
Range("y94").GoalSeek Goal:=0.21, ChangingCell:=Range("x94")
Range("ab89").Select
Range("ab89").GoalSeek Goal:=0.21, ChangingCell:=Range("aa89")
Range("ab90").Select
Range("ab90").GoalSeek Goal:=0.21, ChangingCell:=Range("aa90")
Range("ab91").Select
Range("ab91").GoalSeek Goal:=0.21, ChangingCell:=Range("aa91")
Range("ab92").Select
Range("ab92").GoalSeek Goal:=0.21, ChangingCell:=Range("aa92")
Range("ab93").Select
Range("ab93").GoalSeek Goal:=0.21, ChangingCell:=Range("aa93")
Range("ab94").Select
Range("ab94").GoalSeek Goal:=0.21, ChangingCell:=Range("aa94")
End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Try a variable or constant:

Const lngFactor As Long = 0.21

Range("G7").GoalSeek Goal:=lngFactor, ChangingCell:=Range("F7")
etc.
 
Upvote 0
Rather a long bit of code.

Does this do what you want?

Code: 
Sub Goalseeker()
Dim goalCol As Long, changeRow As Long, goalRow As Long
 
For goalCol = 7 To 28 Step 3
 
    changeRow = 7
 
    For goalRow = 7 To 9 Step 1
 
        Cells(goalRow, goalCol).GoalSeek Goal:=Range("A7"), ChangingCell:=Cells(changeRow, goalCol - 1)
 
        changeRow = changeRow + 1
 
    Next goalRow
 
Next goalCol
 
End Sub

Dom
 
Last edited:
Upvote 0
mistyped....

That code is a bit complicated for me to understand as I am sort of a newbie. "a7" will be a constant that I will change based on what I want my goal to be (0.22, 0.23, etc...). I just didn't know if this was as simple as changing "=0.21" to "=$a$7"
 
Upvote 0
To be honest I should have looked down your code further as I thought the pattern was obvious whereas my code will only fulfill the first part.

To reference a cell use =Range("A7").Value

Will have a look and see if I can simplify things a bit later.

Dom
 
Upvote 0
Macro recorded always needs to be optimized.
A shorter code:

Code: 
Sub IRR_Macro()
Dim rr As Range, r As Range, i As Byte
Set rr = [g7:g9,g22:g29,g40:g46,g65:g79,g89:g94]
'[a7] = 0.21
For Each r In rr
For i = 0 To 21 Step 3
r.Offset(, i).GoalSeek [a7], r.Offset(, i - 1)
Next
Next
End Sub

Regards
Northwolves
 
Upvote 0
Northwolves-

That worked great, however I have a question. I wanted to setup another macro with just a section of the first macro, say....

Sub IRR_Macro_1()
'
' IRR_Macro_1 Macro
' Macro recorded 3/13/2008 by Beau Ryan
'

'
Range("G7").Select
Range("G7").GoalSeek Goal:=0.22, ChangingCell:=Range("F7")
Range("G8").Select
Range("G8").GoalSeek Goal:=0.22, ChangingCell:=Range("F8")
Range("G9").Select
Range("G9").GoalSeek Goal:=0.22, ChangingCell:=Range("F9")
Range("J7").Select
Range("J7").GoalSeek Goal:=0.22, ChangingCell:=Range("I7")
Range("J8").GoalSeek Goal:=0.22, ChangingCell:=Range("I8")
Range("J9").Select
Range("J9").GoalSeek Goal:=0.22, ChangingCell:=Range("I9")
Range("M7").Select
Range("M7").GoalSeek Goal:=0.22, ChangingCell:=Range("L7")
Range("M8").Select
Range("M8").GoalSeek Goal:=0.22, ChangingCell:=Range("L8")
Range("M9").Select
Range("M9").GoalSeek Goal:=0.22, ChangingCell:=Range("L9")
Range("P7").Select
Range("P7").GoalSeek Goal:=0.22, ChangingCell:=Range("O7")
Range("P8").Select
Range("P8").GoalSeek Goal:=0.22, ChangingCell:=Range("O8")
Range("P9").Select
Range("P9").GoalSeek Goal:=0.22, ChangingCell:=Range("O9")
Range("S7").Select
Range("S7").GoalSeek Goal:=0.22, ChangingCell:=Range("R7")
Range("S8").Select
Range("S8").GoalSeek Goal:=0.22, ChangingCell:=Range("R8")
Range("S9").Select
Range("S9").GoalSeek Goal:=0.22, ChangingCell:=Range("R9")
Range("V7").Select
Range("V7").GoalSeek Goal:=0.22, ChangingCell:=Range("U7")
Range("V8").Select
Range("V8").GoalSeek Goal:=0.22, ChangingCell:=Range("U8")
Range("V9").Select
Range("V9").GoalSeek Goal:=0.22, ChangingCell:=Range("U9")
Range("Y7").Select
Range("Y7").GoalSeek Goal:=0.22, ChangingCell:=Range("X7")
Range("Y8").Select
Range("Y8").GoalSeek Goal:=0.22, ChangingCell:=Range("X8")
Range("Y9").Select
Range("Y9").GoalSeek Goal:=0.22, ChangingCell:=Range("X9")
Range("AB7").Select
Range("AB7").GoalSeek Goal:=0.22, ChangingCell:=Range("AA7")
Range("AB8").Select
Range("AB8").GoalSeek Goal:=0.22, ChangingCell:=Range("AA8")
Range("AB9").Select
Range("AB9").GoalSeek Goal:=0.22, ChangingCell:=Range("AA9")
End Sub


....How would I then modify what you previously posted?
 
Upvote 0
Only [a7] 's change is necessary.
Code: 
Sub IRR_Macro()
Dim rr As Range, r As Range, i As Byte
Set rr = [g7:g9,g22:g29,g40:g46,g65:g79,g89:g94]
[a7] = 0.22
For Each r In rr
For i = 0 To 21 Step 3
r.Offset(, i).GoalSeek [a7], r.Offset(, i - 1)
Next
Next
End Sub

Regards
Northwolves
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Goal Seek Macro in VBA Giving Nonsensical Answers
Replies
3
Views
2K
StephenCrump
S
C
  • Question
Faster Goal Seek / Copy Past Macro
Replies
2
Views
7K
chet645
C
L
  • Solved
COUNTIF with multilayered dropdown selections
2
Replies
11
Views
557
Eric W
Eric W
J
  • Question
Macro to Run Goal Seek and Grab Certain Info
Replies
0
Views
302
jason8490
J
S
  • Question
Macro for Goal Seek on Multiple Cells
Replies
0
Views
8K
ShanaVT
S

Forum statistics

Threads
1,215,452
Messages
6,124,914
Members
449,195
Latest member
Stevenciu

Share this page

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
Back
Top