Sub multi_guess_and_test()
Dim ws_input As Worksheet
Dim num_rows
Set ws_input = ThisWorkbook.Worksheets("Input")
num_rows = 5
Dim i
Dim j
Dim k
Dim l
Dim set_cell_range As Range, to_value_range As Range, changing_cell_range As Range
Dim to_value_val
Dim temp_val
Dim temp_range As Range
For j = 2 To num_rows
For i = 2 To num_rows 'i starts at 2 because we assume 1st row is label
k = i + 7
Set set_cell_range = Range(ws_input.Cells(k, j).Formula)
On Error Resume Next
l = i + 13
Set to_value_range = Range(ws_input.Cells(l, j).Formula)
If Err.Number <> 0 Then
to_value_val = ws_input.Cells(l, j).Value 'find the value of cell. Even if the cell is a formula, it is ok! (Originally, goal Seek does not allow this cell to be a formula.)
End If
On Error GoTo 0
Set changing_cell_range = Range(ws_input.Cells(i, j).Formula)
'now that all the inputs are set, perform goal seek operation
set_cell_range.GoalSeek _
Goal:=to_value_val, _
ChangingCell:=changing_cell_range
Next 'onto the next calculation!
Next
End Sub
i am trying to call values using a for loop and is giving an error please help me.
thank in advance.
Bookmarks