Excel 2002 macro

[Closed]
Report
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009
-
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009
-
Hello,
Sub goalSeekSample()
Dim seekValue As Double
Dim changeCell As Range
seekValue = InputBox("What's the value to seek?")
For Each changeCell In Range("N14:N32").Cells
changeCell.GoalSeek Goal:=seekValue, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell
End Sub

This macro opens a box, i type a number and it goes down the software using goal seek to achieve that number on each row.
it works fine.
but instead of inputing the number, i would like it to look at the cell to the right (column "O" ) and use that number.

note recording the number wont work cos goal seek wont accpet cell addresses.
thanks all

9 replies

Can you be more descriptive about the values you are comparing or wanting to change in column O?
I f you want the number in column N to equal the number in column O then try this.

Private Sub ChangeValue()
Dim i
Dim x
i = 13
x = 14

Do Until Range("O" & x) = Range("O33")

If IsNumeric(Range("O" & x)) Then
i = i + 1
Range("N" & i) = Range("O" & x)
End If

x = x + 1
Loop

End Sub
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009

Thanks very much for the macro.
problem now is, i had hoped to just paste that into another macro, removing all the old macro.
but when i try to run it - tool-macro - well it aint rhere!
suggestions please!

how do i post the excel file here?

http://www.4shared.com/file/84814527/8298b493/Copy_of_RRIF_2009.html

here is the file

I would like the blue values in column N from row 14 to 46 to equal the values in the same row in column O.
row 14 to 46
thanks
Thanks for sending the file. That made it much easier to see what you wanted.

Here is what I suggest.
Instead of going to Macros after you click Tools, select Visual Basic Editor.
On the left side of the pane window, you will see a folder called Modules.
Click on the plus sign to expand the folder. Modules 3 and Modules 6 have the code that you want to delete.
You have to delete each Module one at a time.
Right click on the Module and select Remove Module....
Now close the Visual Basic Editor to return to your spreadsheet.
Click the Record Macro button. Now, immediately click Stop Recording.
Click Tools >>Macros, and select the macro you just recorded.
Now rename the macro to match the name on your spreadsheet.
Then click Edit macro and paste the code I gave you.
You are done.
Aslo, I modified the code a little since I was able to work with the file (Thanks again for that).
It is simplified and should run faster.

Dim i
Dim x
i = 14
x = 14

Do Until Range("O" & x) = Range("O33")


Range("N" & i) = Range("O" & x)

i = i + 1
x = x + 1

Loop
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009

WOW!!!!
thanks very much!
esp for the clear instructions.
i also see what you mean by fast.

what do i do about Run time error '28'

out of stack space?
thanks again

debug leads to
Sub Inflation()
Dim i
Dim x
i = 14
x = 14

Do Until Range("O" & x) = Range("O33")


Range("N" & i) = Range("O" & x)

i = i + 1
x = x + 1

Loop
Application.Run "'Copy of RRIF 2009macro test.xls'!Module3.Inflation"
ActiveWindow.SmallScroll Down:=9
ActiveWorkbook.Save
Range("K20").Select
ActiveWindow.SmallScroll Down:=-6
Range("L13").Select
ActiveCell.FormulaR1C1 = "15000"
Range("L14").Select
End Sub
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009

BIG OOPS
it is only putting that number in column N.
it needs to put a number in column J so that the end result - calculated result - is the same as column O.
i should have made that clear.
that is why the old macro did "goal seek'
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009

calculated result of course is in column N - net income
I'm trying to modify your original macro with the goal seek.
This is where we have to go back to your original question.
You said you wanted Column N to look in Column O without using the inputbox.
So, the value to start in N14 equals O14 until it reaches N32 like your original macro?

Example:
The value in O14 in the spreadsheet is 26206. You want that as the value in N14:N32 using goal seek.


Once we get past this, we can tackle the next obstacle.
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009

thanks for persisting.
your answer is very close.
the "o" column increase with inflation.
so each N row will be different from that above it by the inflation amount.

O7 is the inflation amount Cell name 'inflation' if that is an easier way to do it!
thanks again
I think this is what you are looking for. The problem with this code is, you would have to assign it to a button, and keep clicking the button until it reached N32. The code is assigned to only one cell, and then the For loop exits so it can check the value of the next cell. I have not used goal seek in vba before so I keep getting syntax errors.
In your original code you called for the inputbox to fill the range N14:N32 thus, using the same number to fill the range. Maybe this can help you.

Dim seekValue As Double
Dim changeCell
seekValue = Range("O14")
seekValue2 = Range("O15")
seekValue3 = Range("O16")
seekValue4 = Range("O17")
seekValue5 = Range("O18")
seekValue6 = Range("O19")
seekValue7 = Range("O20")
seekValue8 = Range("O21")
seekValue9 = Range("O22")
seekValue10 = Range("O23")
seekValue11 = Range("O24")
seekValue12 = Range("O25")
seekValue13 = Range("O26")
seekValue14 = Range("O27")
seekValue15 = Range("O28")
seekValue16 = Range("O29")
seekValue17 = Range("O30")
seekValue18 = Range("O31")
seekValue19 = Range("O32")



For Each changeCell In Cells(14, 14)
changeCell.GoalSeek Goal:=seekValue, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell


For Each changeCell In Cells(15, 14)
changeCell.GoalSeek Goal:=seekValue2, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell


For Each changeCell In Cells(16, 14)
changeCell.GoalSeek Goal:=seekValue3, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell


For Each changeCell In Cells(17, 14)
changeCell.GoalSeek Goal:=seekValue4, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(18, 14)
changeCell.GoalSeek Goal:=seekValue5, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(19, 14)
changeCell.GoalSeek Goal:=seekValue6, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(20, 14)
changeCell.GoalSeek Goal:=seekValue7, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(21, 14)
changeCell.GoalSeek Goal:=seekValue8, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(22, 14)
changeCell.GoalSeek Goal:=seekValue9, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(23, 14)
changeCell.GoalSeek Goal:=seekValue10, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(24, 14)
changeCell.GoalSeek Goal:=seekValue11, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(25, 14)
changeCell.GoalSeek Goal:=seekValue12, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(26, 14)
changeCell.GoalSeek Goal:=seekValue13, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(27, 14)
changeCell.GoalSeek Goal:=seekValue14, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(28, 14)
changeCell.GoalSeek Goal:=seekValue15, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(29, 14)
changeCell.GoalSeek Goal:=seekValue16, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(30, 14)
changeCell.GoalSeek Goal:=seekValue17, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(31, 14)
changeCell.GoalSeek Goal:=seekValue18, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell

For Each changeCell In Cells(32, 14)
changeCell.GoalSeek Goal:=seekValue19, _
ChangingCell:=changeCell.Offset(0, -4)
Next changeCell


End Sub
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009

Thanks
Compile error
Invalid outside procedure

on the O14

and even when i close the editor, the macro is not on the spreadsheet.

Please let me know if you get it working on the spreadsheet i posted
your help is very much appreciated
thanks
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009

I was thinking -
we could keep the original macro and just put something like
value =value+(value+inflation%)
in the loop.
how do i code that so it understands me!
thanks
Posts
9
Registration date
Thursday February 5, 2009
Status
Member
Last seen
February 8, 2009

Sub goalSeekSample()
Dim seekValue As Double
Dim changeCell As Range
Inflation = Range("O7").Value
seekValue = InputBox("What's the value to seek?")
For Each changeCell In Range("N14:N32").Cells
changeCell.GoalSeek Goal:=seekValue, _
ChangingCell:=changeCell.Offset(0, -4)
seekValue = seekValue + (seekValue * Inflation)
Next changeCell
End Sub

this work
problem solved