Offset in Goal Seek

[KateWantsToLearn]

Hi there!

I'm trying to do a goal seek with an offset but i haven't written code (and even when I did only took an intro class) in 5 years! Sos

I am trying to change the value in SHEET1 cell E49 equal to the VALUE in cell E436 on the same sheet ("SHEET1") by changing cell E38 on tab SHEET 2. I have no problem doing this with the following code:

<!-- BEGIN TEMPLATE: bbcode_quote -->
Quote:
<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">Worksheets("SHEET1").Range("e49").GoalSeek Goal:=Range("e436").Value, changingcell:=Worksheets("SHEET2").Range("e38") </TD></TR></TBODY></TABLE>
<!-- END TEMPLATE: bbcode_quote -->But I need to perform this same function for 150 columns (column e is the first one, so it goes throught column ex). I tried making the code below, but aside from all of the other things that you'll clearly see are wrong with it, I think this would offset my first value, too, when I would want to have the very first iteration (column e) not be offset. I assume i need some sort of i +1 or something?

<!-- BEGIN TEMPLATE: bbcode_quote -->
Quote:
<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">Dim CELL as range
For Each CELL In worksheets(“sheet2”).Range("e38:ex150")
Application.Goto Worksheets("sheet1").Range("e49").Offset(0, 1)
ActiveCell.GoalSeek Goal:=ActiveCell.Offset(0, 1).Range_("e436"), changingcell:=ActiveCell.Offset(0, 1).worksheets(“sheet2”).Range_("e38") </TD></TR></TBODY></TABLE>
<!-- END TEMPLATE: bbcode_quote -->Some help, pretty please?
Also...eventually i will need to repeat the corrected macro that calcs for e:ex every 12th row (i'm afraid my excel will implode!) - if you could help with that, too, that'd be awesome, but maybe i can figure that out after seeing the answer...

Happy Fourth of July!<!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[=ODIN=]

try this (assumes you are running macro from sheet1)

Sub bsKate()
    On Error GoTo errDepot
    For i = 5 To 150
        ActiveSheet.Cells(49, i).GoalSeek Goal:=ActiveSheet.Cells(436, i).Value, changingcell:=Worksheets("SHEET2").Cells(38, i)
    Next i
    
    Exit Sub
errDepot:
    'do nada
End Sub

 

[KateWantsToLearn]

Hi (=ODIN=)!!

Thank you for taking the time to help me! Unfortunately, my project has changed such that now I don't need to worrk about the columns 5 - 150 anymore, i just need to worry about one column, but my rows will have a changing start value for everytime i run a record. I tried to adjust your code and give it a "step" function, but it's not working right.

Sub MrXLAttempt()

For i = Range("j1").Value To Range("j2").Value Step 12
'Range("j1").value returns the number 20, which is the first row that i need to have the goal seek happen in, Range "j2" returns the number 380, which is the last row where I want it to happen and I'm trying to make it happen every 12th row after the first occurence (in this particular case, row 20).  I'm doing this all wrong though, because I get a run time error '1004 that highlights my goal seek piece.
            
ActiveSheet.Cells(i, 10).GoalSeek Goal:=ActiveSheet.Cells(i, 12).Value, changingcell:=ActiveSheet.Cells(i, 8)
            
Next i

End Sub

Any suggestions?

 

[=ODIN=]

Hey Kate,

As far as I can tell your code modification is fine. I just tested it myself and it worked.

I put 20 in J1
380 in J2

Column L - I stuck in even numbers all the way down past row 380
Column J - I ented the formula (=H# * 2)

I then ran your code and in Column H I found every 12th row starting from row 20 and ending at 380 a value that was half that in row L.

That is what I would expect to find from you code. I assume you have your spreadsheet setup with the data setup like this?

 

[KateWantsToLearn]

You know what I just realized - my code (shockingly!) does work - you're right. My problem is that - remember how on all the non-goal seeking cells I had the formula so that it = the cell above it? I had =s in all of the cells this time and you can't have a formula there, so that's why my code wouldn't run.

I'm trying to figure out how to write my code so that the = will show up only in rows that don't need a goal seek. Those rows are the only ones that have values that i'm setting the year end total to, so i was trying to do something like this:

for each cell in ChangingRange
 
if cell = "" then [insert formula here for putting that cell equal to the cell above it]

the problem is...it's not if the cell in my range is blank, it should be if the cell two columns to the right of it is blank...this is so tough to learn!

Also, i assume mine is running a lot slower than yours becaues i didn't dim my variables?

 

[=ODIN=]

If I understand you correctly, you have formulas in all the cells in column h, which is not allowed in goal seek. A quick easy way to solve that would be to just delet the cell value right before you run goalseek on it.

Sub MrXLAttempt()
 
For i = Range("j1").Value To Range("j2").Value Step 12
 
'Range("j1").value returns the number 20, which is the first row that i need to have the goal seek happen in, Range "j2" returns the number 380, which is the last row where I want it to happen and I'm trying to make it happen every 12th row after the first occurence (in this particular case, row 20).  I'm doing this all wrong though, because I get a run time error '1004 that highlights my goal seek piece.
[B]activesheet.cells(i,8).value = ""[/B]            
ActiveSheet.Cells(i, 10).GoalSeek Goal:=ActiveSheet.Cells(i, 12).Value, changingcell:=ActiveSheet.Cells(i, 8)
 
Next i
 
End Sub

the dim ing your variables won't make this code noticably faster for you. it is just good practice and helps avoid errors.

 

[KateWantsToLearn]

Did you ever know that you're my hero???????????????????????

Thank you so much for your help!!!!!! This fixed my problem!

 

[=ODIN=]

You are the wind beneath my wings

Glad I could help.