VBA Coding - Excel 2007

Need help with coding in VBA for Excel 2007

I'll try to make this as short as possible...

I have a workbook called "Modifications" which consists of two (2) worksheets called "Mod Calcs" and "Formulas".

The "Mod Calcs" sheet contains a cell (N13) which uses VLOOKUP to find a specific formula on the "Formulas" sheet.

The formula referenced on the "Formulas" sheet is a function of user-inputs from the "Mod Calcs" sheet. Therefore, I need the formula on the "Formulas" sheet to reference, and pull values from, the active row on the "Mod Calcs" sheet.
I can't figure out any "in cell" formulas to do this so I'm assuming I'll need to use VBA coding...

Here is the Psuedo-Code (for a specific row):
-------------------------
'Mod Calcs'!N13 = VLOOKUP(B13,Formulas!A4:F27,4,FALSE)

'Mod Calcs'!N13 now references Formulas!C9

Formulas!C9 = 'Mod Calcs'!C13*'Mod Calcs'!D13
--------------------------

Can anyone write an example of what this code would look like?
P.S. I understand that it may be easier in FormulaR1C1 format...

I do NOT want the calculations/formulas/references to be hard-coded (i.e. $a$1). That way I can repeat the formats & formulas over a range of rows with each row's calculations being independent.

Thanks in advance,

I wasn't able to find any function called "Evaluate"...

Not sure how I can show you an example "in action"...??

The only piece of the code I need is to be able to do the following:

Determine the row of an active/referenced cell that is on a worksheet different than the one that will contain the formula.

I think you mean you want the result of one formula (your vlookup function) to be another forumala (for example Pi*radius^2 where radius is a cell reference) You basically want to convert text to a formula.

If what I assume is correct... I think you're right in that an "evaluate" formula does not exist. But why not evaluate the formulae results on the "Formula" sheet as a new column in your vlookup range. Then the vlookup will return the result rather than the formula. Not sure if this applies to your situation. You could even use Vlookup to display the input descriptions (ie radius) and input units...

I actually have it set up as you've describe already. The VLOOKUP function is referencing a value the "Formulas" sheet, not the actual formula itself.
But, the formula on "Formulas" still requires values from the "Mod Calcs" sheet and these values are variable (they can come from different rows).
Therefore, I can't use a specific cell, or range, in the formula. The formula requires data from the row on the "Mod Calcs" sheet that is using the VLOOKUP function.

It's kind of a cycle that is using both sheets and referencing variable values on both, but without creating a "self reference formula"

My apologies...

I didn't realize Evaluate was only for VBA, I'll see if it helps.
And there was confusion on the N13 value... It does NOT pull the formula, but instead pulls the value (placed in to a separate cell) that the formula outputs.
However, the formula is to take cell values as inputs and that's where i'm having the issue.
The cell values that are to be used as inputs are solely related to the row that is calling the VLOOKUP for the formula.

I'll try an example:
'Mod Calcs'!N16 will VLOOKUP the value in 'Mod Calcs'!B16 and return the value found inside cell 'Formulas'!D9.
The value inside cell 'Formulas'!D9 is derived from the function in cell 'Formulas'!D8.
The function inside of cell 'Formulas'!D8 is a function of the cells 'Mod Calcs'!C16 & 'Mod Calcs'!E16.

Now... I can't hard-code the cell references because in another row, row 17 for example, the VLOOKUP may reference that same function ('Formulas'!D8) but the function would now require inputs from 'Mod Calcs'!C17 & 'Mod Calcs'!E17.

The problem is that the formula/function on one sheet is always the same, but the formula/function inputs on the other sheet will always be based upon the row number.
I can't seem to find any code that will allow me to store the "active" row number of a worksheet that isn't actually in use.

As I don't entirely understand your problem, this is sort of a stab in the dark...

I think your formulas on your "formula" sheet need to be able to return values for multiple sets of data simultaneously to different lines of your "mod calcs" sheet. For example, I think that right now in your formula page, your area formula is on line 1, your volume formula on line 2, and your temp formula on line 3. You want to do a lookup to select the correct formula then return the result.

If this is the case, maybe you need to perform all possible calculations (in your formula sheet) on all lines of data in your "mod calcs" sheet. In this case, your formula and results for area would occupy the first few columns, then volume would occupy the next few columns, then temp..... Each line in your formulas sheet would reference each line in your mod calcs sheet. To get only the result for the calculation you want to perform, you would need another lookup table.

Please Login or Register  to view this content.

This lookup would be nested in your result lookup and indicate which column to pull the result from... Hope this makes sense...

Alternatively, you could create your own formula with your own inputs using VBA. More info would be helpful.