Microsoft Office Forums - View Single Post - [Solved] VBA with goal seek takes too long

Stata15 · 07-20-2015, 05:49 AM

Hi,

I have got an issue with running a VBA command. The command repetes the goal seek function for all my observations (one oberservation=one line). Since I have a total of 670.000 oberservations it takes a very long time for excel to compute all the numbers.

A friend of mine helped to build the VBA command:

Code:

Public Function Zielwert()
Dim z As Integer
 
z = 2
 
For i = 1 To 500
 
  Range("EY" & z).GoalSeek Goal:=Range("K" & z).Value, ChangingCell:=Range("EW" & z)
  Range("EY" & z).GoalSeek Goal:=Range("K" & z).Value, ChangingCell:=Range("EW" & z)
 
  z = z + 1
Next i
 
End Function

I tried to run the VBA for 500 observations and for 1000. For 1000 it takes not twice as much time but way more time! And 10.000 observations do not need 10x as much but more than a night to finish.

So I need a solution for how to perform the goal seek function with VBA but a lot faster than right now. Maybe a change in the VBA command is required or several VBA commands in different sheets, since it runs faster with smaller numbers of observations?

Does anyone know what to do?
Thanks in advance!