I have a problem that I am certain is challenging only because of my primitive knowledge of VBA, but would appreciate anyone's insight. I have tried to write a short macro which identifies a certain cell in a range, and then performs a goal seek by changing the value of the identified cell. The code I have so far is:
' GoalSeekEPCMarginRequirement Macro ' ' Set Variables Dim ActiveCase As Long Dim EPCMargins Dim EPCMargin ActiveCase = WorksheetFunction.Match(Range("ActiveCase"), Range("Scenarios"), 0) EPCMargins = Range("EPCMargins") EPCMargin = EPCMargins(1, ActiveCase).Range ' Set XIRR Value for Goalseek to Owner ATIRR Hurdle Range("TotalULXIRR").Value = Range("OwnerATIRRHurdle").Value ' Goalseek Range("TotalULXNPV").GoalSeek Goal:=0, ChangingCell:=EPCMargin End Sub
The syntax "EPCMargin = EPCMargins(1, ActiveCase)" seems to work for identifying the value of the cell in the range "EPCMargins" according to the value of "ActiveCase", which identifies column, but I am not sure how to get an address from this to use in the goalseek function. Any ideas?