Looping of repeatitive formula

**Chetan Chauhan** · 09-04-2009, 06:54 AM

Hi there,

I have 8 sheets in a workbook in which I have to run 4 vlookup formula in each sheet from other workbook name as 'Yesterday'. The sheet name are as follows:

Missing Factor POS, Missing Factor PAM, Missing CpN ABS POS, Missing Cpns for Muni POS, Missing Cpn CORP POS, Missing Cpn ABS PAM, Missing Cpns for Muni PAM, Missing Cpn CORP PAM
The problem is my vlookup formula take the sheet name as reference which makes it difficult to loop it. Is there anyway that it skips to next sheet.

Pls have a look into the code

PHP Code:


ActiveCell.FormulaR1C1 = _ 
        "=VLOOKUP(RC[-1],'[Yesterday.xls]Missing Factor POS'!C17:C18,2,0)" 
ActiveCell.FormulaR1C1 = _ 
        "=VLOOKUP(RC[-2],'[Yesterday.xls]Missing Factor POS'!C17:C19,3,0)" 
 ActiveCell.FormulaR1C1 = _ 
        "=VLOOKUP(RC[-2],'[Yesterday.xls]Missing Factor POS'!C17:C20,4,0)" 
   ActiveCell.FormulaR1C1 = _ 
        "=VLOOKUP(RC[-2],'[Yesterday.xls]Missing Factor POS'!C17:C21,5,0)"

**blackworx** · 09-04-2009, 01:16 PM

you'd want something like this

Please Login or Register  to view this content.

the above will insert the formulas on any worksheet whose codename begins with "doCALC_". you'll have to specify the cells where the formulas want to go (where it says ".Cells(r,c)" in the code you'll need to replace r & c with the required row and column) as from your code there are obviously different formulae going into different cells, but they're referenced by ActiveCell and I'm guessing you trimmed the code.

**Chetan Chauhan** · 09-08-2009, 02:20 AM

Hi there,

I am sorry I am not so good in VBA but i tried your trick by replacing the 'doCALC_' with 'missing' word as this is common in all the worksheet. The macro skips to the End If command without reading the formula.

And I replace the (r,c) with (2,18) in the first formula but its not reading the same.

Can you help me out

Thanks in advance.

**blackworx** · 09-08-2009, 04:55 AM

Hi Chetan

With regard to "doCalc_" I'm guessing you have changed the tab name for the sheet rather than the code name. To make the macro look at the tab name instead, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

You also need to replace the number 7 with the actual number of characters in your chosen prefix. e.g. if your sheets all begin with "MyPrefix" then you would replace the 7 with an 8 - the length of "MyPrefix".

With regard to the 2,18 - I'm not sure what you mean? Replacing r,c with 2,18 should cause the formula to be placed in cell R2 (row 2, column 18). Bear in mind that your original code did not indicate where to place the formulas, as it was using "ActiveCell" and there was nothing in the code to indicate what the currently selected cell was at the time. Because FormulaR1C1 works with relative references, you must make sure the formula is being placed in exactly the same cell as before.

Hope that helps. If not attach a workbook with your code and I'll take a look.

bwx