PDA

View Full Version : Goal Seek for multiple rows with blanks between them



eureka18
12-13-2010, 09:27 AM
Hello everyone,

At the moment I am busy doing a research for my studies. In this research I need to make use of the Goal Seek function in Excel:

Set cell A1 (a formula) equal to cell B1 (input) by changing cell C1 (number)

However, I have to do this for 1000 individual cases in the same sheet but with empty rows between them (In other columns there is data on these rows). To explain my problem further I attached an example file of what I mean.

I have looked on the internet but I could not find a working vba function on this problem. I have no experience in programming but I was wondering if someone could help me figuring out this problem.

Eureka18

eureka18
12-14-2010, 06:07 AM
Ok I tried something myself but of course it does not work

Sub Macro1()
For number As cells = 5 To 12
If Not (Range("D(number)=0))Then Range("E5").GoalSeek Goal:="C1", ChangingCell:=Range("B5"); Else skip
Next Number
'D(number) should be referencing to column D row 5 to 12
'If function should first check if cell is empty. If it is not it should exercise the goal function
'if the cell is empty it should skip the row and go one row down to repeat the process
End Sub


Can somebody please help me?

With regards,


Eureka18

eureka18
12-14-2010, 09:43 AM
After a day of hard work I have the following with the example I posted in my first post:


Sub goalseek()
For Each cll In Range("E5:E12")
If cll.Value <> "" Then cll.goalseek Goal:=”$C$1”, ChangingCell:=cll.Offset(, -3)
Next cll
End Sub


However, it does not work, it gives an error at


cll.goalseek Goal:=”$C$1”, ChangingCell:=cll.Offset(, -3)


I do not know why.

Please can someone help???

eureka18
12-15-2010, 03:54 AM
Please people help me out here....

Tinbendr
12-15-2010, 02:39 PM
how about
cll.goalseek Goal:=Range("$C$1").Value, ChangingCell:=cll.Offset(, -3)