VBA Excel - Solver in the loop returns error on the reference to changing cells and conditions -

i trying loop solver command vba excel, , getting error message regarding variables , conditions in model. idea of model find range of debt , equity financing cash balance zero, , debt , equity components meet covenants (that work bounds in solver run).

here code:

sub debt_capital_balancing()    application.screenupdating = false    dim early_repmnt string, cashbeforesolver variant, ced variant, _   dr variant, cc variant, tw single, nde single, de single, w single    k = range("forecast_periods").count   range("debt_received, debt_early_repayment, re_distribution, _ cc_apic_change").clearcontents    = 1 k      cashbeforesolver = abs(range("cash_excess_deficit").cells(1, i).value)     ced = range("cash_excess_deficit").cells(1, i).value     dr = range("debt_received").cells(1, i).value     cc = range("cc_apic_change").cells(1, i).value     tw = range("target_wacc").cells(1, i).value     nde = range("net_debt_to_ebitda").cells(1, i).value     de = range("d_e").cells(1, i).value     w = range("wacc").cells(1, i).value      solverreset     solverok setcell:=ced, maxminval:=3, valueof:=0, bychange:="dr,cc", engine:=3, enginedesc:="evolutionary"      solveradd cellref:=dr, relation:=3, formulatext:=0     solveradd cellref:=cc, relation:=3, formulatext:=0     solveradd cellref:=dr, relation:=1, formulatext:=cashbeforesolver     solveradd cellref:=cc, relation:=1, formulatext:=cashbeforesolver     solveradd cellref:=nde, relation:=1, formulatext:="target_net_debt_to_ebitda"     solveradd cellref:=de, relation:=1, formulatext:="target_d_e_ratio"     solveradd cellref:=w, relation:=1, formulatext:=tw      solveroptions maxtime:=0, iterations:=0, precision:=0.00001, _     convergence:=0.0001, stepthru:=false, scaling:=true, assumenonneg:=false, derivatives:=1      solveroptions populationsize:=100, randomseed:=0, mutationrate:=0.075, _     multistart:=false, requirebounds:=true, maxsubproblems:=0, maxintegersols:=0, _     inttolerance:=0.1, solvewithout:=false, maxtimenoimp:=200      solversolve    next  end sub 

i guess incorrectly refer variables in solver pattern. can please me that? may there other ways refer changing cells , dynamic conditions?

many thanks,

yury

ok, i've found solution, may not optimal works. here code:

sub debt_capital_balancing()  application.screenupdating = false application.calculation = xlcalculationautomatic  dim initcashbalance variant dim initcashbalance2 variant dim targetcell variant dim debtreceivedchangecell variant dim debtpaidchangecell variant dim rechangecell variant dim capitalchangecell variant dim deconstr variant dim waccconstr variant dim dtoebitdaconstr variant dim targwacc variant dim targde variant dim targdtoebitda variant dim debtcfconstr variant dim equitycfconstr variant dim minde variant  range("debt_received, debt_early_repayment, dividends, _ re_distribution, cc_apic_change").clearcontents  k = range("forecast_periods").count  set initcashbalance = range("cash_excess_deficit").cells(1, 1) set targetcell = range("cash_excess_deficit").cells(1, 1) set debtreceivedchangecell = range("debt_received").cells(1, 1) set debtpaidchangecell = range("debt_early_repayment").cells(1, 1) set rechangecell = range("re_distribution").cells(1, 1) set capitalchangecell = range("cc_apic_change").cells(1, 1) set dtoebitdaconstr = range("net_debt_to_ebitda").cells(1, 1) set debtcfconstr = range("debt_cf").cells(1, 1) set equitycfconstr = range("equity_cf").cells(1, 1) set targdtoebitda = range("target_net_debt_to_ebitda")  = 1 k  initcashbalance2 = abs(initcashbalance)  solverreset solverok setcell:=targetcell.address, maxminval:=3, valueof:=0, _ bychange:=debtreceivedchangecell.address & "," & capitalchangecell.address,_ engine:=3, enginedesc:="evolutionary"  solveradd cellref:=debtreceivedchangecell.address, relation:=3, formulatext:=0 solveradd cellref:=capitalchangecell.address, relation:=3, formulatext:=0 solveradd cellref:=debtreceivedchangecell.address, relation:=1, formulatext:=initcashbalance2 solveradd cellref:=capitalchangecell.address, relation:=1, formulatext:=initcashbalance2 solveradd cellref:=dtoebitdaconstr.address, relation:=1, _ formulatext:=targdtoebitda solveradd cellref:=debtcfconstr.address, relation:=3, formulatext:=0 solveradd cellref:=equitycfconstr.address, relation:=3, formulatext:=0  solveroptions maxtime:=0, iterations:=0, precision:=0.00001, _ convergence:=0.0001, stepthru:=false, scaling:=true, _ assumenonneg:=false, derivatives:=1  solveroptions populationsize:=100, randomseed:=0, mutationrate:=0.075, _ multistart:=false, requirebounds:=true, maxsubproblems:=0, _ maxintegersols:=0, inttolerance:=0.1, solvewithout:=false, maxtimenoimp:=200  solversolve userfinish:=true  solverfinish keepfinal:=1   set initcashbalance = initcashbalance.offset(0, 1) set targetcell = targetcell.offset(0, 1) set debtreceivedchangecell = debtreceivedchangecell.offset(0, 1) set debtpaidchangecell = debtpaidchangecell.offset(0, 1) set rechangecell = rechangecell.offset(0, 1) set capitalchangecell = capitalchangecell.offset(0, 1) set dtoebitdaconstr = dtoebitdaconstr.offset(0, 1) set debtcfconstr = debtcfconstr.offset(0, 1) set equitycfconstr = equitycfconstr.offset(0, 1)  next  application.calculation = xlcalculationautomatic application.screenupdating = true  end sub