switch code in cell into code for vba

**Davo** · 10-12-2009, 12:11 PM

Is there a way to convert a code that you put in a cell like this one

HTML Code:

into code in vba?

P17 is the cell that I have this coding in - it will determine if there is text in C17 and if there is it will pull up the pricing and markup associated with it and put the resale price in p17.
C17 is text. On another sheet of the workbook there is text that associates with numbers (pricing and markups).
N17 is the price (that is associated with C17)
O17 is the markup (associated with C17)
This goes through a range of P17 to p63 so there would need to be a loop. I was thinking along these lines but....

HTML Code:

Anyone have a completely different idea of how to do it or continuing my idea? (the reason I'm wanting it in vba rather than each individual cell is I want to tweak that code to allow the user to enter their own price and markup if they want rather than the one pulled up, but if the code is in the cell then once they type in their numbers the code will be gone (since the cell can't be protected to allow for their input. So the vba code will need to allow for user input, but if there is no user input into cells N(i) and O(i) then it will use the associated numbers.)
Thanks for all help.

**JBeaucaire** · 10-12-2009, 01:31 PM

Maybe a simpler approach to rewriting the whole thing is to just have a macro that you can run anytime you want to RESTORE the original formula to a cell you overtyped the formula?

Here's a macro that will do that. It makes sure your selection is in the column P range, the puts the formula back in. It works for any number of cells you have selected in the range.

Option Explicit

Sub ResetFormula()
Dim Cell As Range

For Each Cell In Selection
    If Not Intersect(Cell, Range("P17:P63")) Is Nothing Then _
        Cell.FormulaR1C1 = _
            "=IF(RC3="""","""",IF(ISERROR(VLOOKUP(RC3,vwlookup,4,FALSE)),IF(RC14="""",0,IF(RC15="""",0,RC14*(1+RC15))),VLOOKUP(RC3,vwlookup,4,FALSE)))"
Next Cell

End Sub

**Davo** · 10-13-2009, 01:07 AM

How would that be applied (sorry I'm a newbie to macros and such). Would I insert a module and put the formula there? How would it determine whether or not to past the formula back, would it determine if there were nothing written in that spot then it would apply it?

Thanks for your time explaining this to me.

**royUK** · 10-13-2009, 02:53 AM

You need to make sure that you understand basic Excel before venturing into VBA. You cannot put code ino a cell, that is a Formula.

I can't really see why you think VBA would be better than a formula approach, attach a workbook example