Goal Seek Macro using Function() for Amazon Pricing

[AYR_RAY]

Hi guys

I have made a macro to calculate the sale Price on Amazon

Dim seller As Integer

seller = Range("c9")

Range("C26").GoalSeek Goal:=seller, ChangingCell:=Range("C12")

End Sub

Where the nett amount payable to vendor is cell c9 than there are calculations which determine the Final price on Amazon is C12

works as follows

for Eg Payable to vendor is 100$


Closing Charge fixed: 10$
Freight Forwarding Charge : 70$
Commision : 8% of final rate on Amazon =16.87$
tax @ 14.5% on all these costs

so final rate will be
Final Sale rate on Amazon = 210.92$ this is calulated by goal seek

Using Goal seek and straight sets of calculations it works on a separate sheet

Now I want to convert this macro into a function which would look a figures in a n excel sheet using offset function or similar do the goal seek calculation and return the value in the cell where the formula is enetred

something like :

Function AMAZONRATE(ByVal PAYABLETOVENDOR)

GoalSeek Goal:=PAYABLETOVENDOR, ChangingCell:=Range("C12") <<<< dont know what to do
End Function

please help

thanksin advance

 

[cerfani]

goal seek doesnt return anything but performs an operation using different ranges. It basically edits the value in the changing cell and the changing cell needs to be referenced by the formula in the range calling goalseek. It is not designed to be used in a UDF. You would need to write your own logic for goal seeking.