And this is why I hate VBA, because Im soooooo crap at it.

So a big favour to ask of any VBA gurus…

Could you start me off with some code to do the following please:

Set up:
There’s a column of 290 ids in colA of sheet1
Next to that an empty columnB in sheet1 to receive a pasted value

Take the first ID from row 1 of ColA in sheet1 copy and paste it into a cell (same cell everytime) in sheet2, wait for a vlookup to populate, run a goalseek, copy an output value in sheet2 and paste value in first cell of columnB of sheet1,
NEXT
copy second ID from colA and repeat, pasting output value in second cell of ColB. Repeat for 290 rows.

Beer tokens available! 🙂

Not knowing the specifics of the calculation you’re carrying out, I may be oversimplifying this, but would a user defined function not be a better way of achieving the same result?

That way you could just call it in a formula in col B of your spreadsheet and copy it down to all the relevant rows.

The process of copying a value to another sheet just to return a calculated value before moving it back again sounds a bit clunky to say the least.

Linky: http://www.techrepublic.com/article/how-do-i-create-a-user-defined-function-in-microsoft-excel/5300300

Because a goalseek is required to solve for the output (big model) it needs to run as a macro, there’s no static solution, otherwise I could have used SCENARIO

VBA offers a goalseek method, which can in turn refer to a cell range so pretty sure you could still do it in a function.

http://msdn.microsoft.com/en-us/library/aa195749(v=office.11).aspx

youve lost me.

I have a large model that’s populated from data sources that are selected by comparing with an id code. Once populated, a goal seek is run to provide a solution by iterating an unknown variable. Once solved for a target that unknown variable is then extracted and stored.

Then another set of data is brought into the model based on a new ID and on again.

Running 290 goalseeks is not something Id want happening in the back ground to keep a UDF fresh, I want to control when it runs.

You wouldn’t be running 290 goalseeks in the UDF – I’m proposing you set up a user defined function to run the Vlookup and Goalseek that you’re currently proposing to do by copying a value between two worksheets.

You can then call that UDF on demand for individual IDs to return your “unknown” variable.

I can do that already, I want a bit of code/macro to run all 290 in a batch. I need the VBA to change the inputs before each running of the goalseek and then to store the output in a table.

Sub testicles()
‘
‘ bingo
‘
For x = 1 To 290
Sheets(“Sheet1”).Select
Cells(x, 1).Copy
Sheets(“Sheet2”).Select
Cells(x, 1).Select
ActiveSheet.Paste

Range(“F5”).GoalSeek Goal:=400, ChangingCell:=Range(“C3”)

Cells(5,6).Copy ‘[whatever the output cell you want to copy]
Sheets(“Sheet1”).Select
Cells(x, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next x

you might want to use activecell.pastespecial xlPasteValues
rather that just activecell.paste
otherwise if you have a formula in sheet1colA it would get messed up

Public Sub mark()

For x = 1 To 5

Sheets(“Sheet2”).Select
Cells(x, 1).Copy
Sheets(“Sheet1”).Select
Cells(x, 1).Select
ActiveCell.Paste

Range(“A3”).goalseek Goal:=1000000, ChangingCell:=Range(“A2”)

Cells(1, 3).Copy
Sheets(“Sheet2”).Select
Cells(x, 2).Select
ActiveCell.Paste

Next x
End Sub

ive created a dummy setup to get the syntax right, but when I play this macro

“Object doesnt support this property or method” when it gets to “ActiveCell.Paste”

❓

EDIT ahah cross posts, will try again

edited

I can do that already, I want a bit of code/macro to run all 290 in a batch. I need the VBA to change the inputs before each running of the goalseek and then to store the output in a table.

I know – I was trying to point out that copying and pasting values between different bits of the spreadsheet isn’t the best way of returning the value you’re after as you could encapsulate that in a function.

Code like Reggie Gasket’s above will give you the means to move through your table. The user defined function would be called within that with the ID from the relevant cell as the parameter.

needs to be Activesheet.paste, not activecell

Got it, you legend reggie xxxxxxxxxx

Sub goalseek()
‘
‘ goalseek Macro
‘
‘ Keyboard Shortcut: Ctrl+w
‘
For x = 1 To 5

Sheets(“Sheet2”).Select
Cells(x, 1).Copy
Sheets(“Sheet1”).Select
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range(“A3”).goalseek Goal:=1000000, ChangingCell:=Range(“A2”)

Cells(2, 1).Copy
Sheets(“Sheet2”).Select
Cells(x, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next x
End Sub

invoice is in the post…

are payments in beer VAT exempt?

thanks again.

Code running fine, although I might need to put some kind of error catcher in there for if there is no solution to the goalseek.

Now just waiting for the client to send me the unlock password to be able to drop the code in the model, at the moment I have to run it from outside. Another reason to have it set up this way, as I cant embed UDF etc in the host model.

put

On Error Resume Next

on the line before the goalseek code

that way if it can’t find a solution it just carries on anyway, like a stubborn child/wife/roadie/singlespeeder

great will try that. Thanks

Just received an unlocked master model, so trying to drop the code in now. I feel like Will SMith in Independance Day 🙂