VBA with goal seek takes too long

Stata15 · #1 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!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatic goal seek program-Help required	calltobala	Excel Programming	0	05-08-2015 08:41 AM
Excel 2013, Problem with a shared file, takes long time to load and save and loads missing data	PStark	Excel	0	12-04-2014 07:52 AM
Excel VBA Multiple Automatic Goal Seek Required – Please Assist us	champaben	Excel Programming	13	06-22-2014 05:54 AM
set daily goal for actual workdays, excluding weekends holidays and days off	Brian Reilly	Excel	1	01-24-2014 11:56 PM
Adding a goal line to a chart	uhlersa	Excel	1	01-16-2012 08:18 PM