Short Macro code for Vlookup not working

[iainmartin100]

Hi,
Below is a short bit of code which seems to work sometimes but not others, if any one has a suggestions to how I can correct it to perform the calculation on all cells everytime that would be great.

Basiclly my aim is to look up a value on the current worksheet then to ask the user to select a file to perform the lookup value.

In the cell F10 I have this formula:
=VLOOKUP(A10,[fname]Hotel!$C:$Z,5,FALSE)

The marco to update is:
Range("F10").Select<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
ActiveCell.Formula = "=VLOOKUP(RC[-5],[fname]Hotel!C3:C26,5,FALSE)"<o></o>
Range("F10").Select<o></o>
Application.CalculateFull

The issue I have is that there are about 20 identical lookups (only variant is the first vlookup refrance A10 which changes to A11, A12 etc),
Sometimes this works and updates all the formulas and other times only the formula in F10 and the other remain unchanged?

Any help would be very greatfully recived.

 

[moonfish]

Try this instead?

Range("F10").Formula = "=VLOOKUP(" & RC[-5] & ",[fname]Hotel!C3:C26,5,FALSE)"
Application.CalculateFull

 

[Norie]

How does it not work?

Also what's fname?

Is that meant to be the filename of the workbook the user has selected?

 

[Richard Schollar]

Hi

So, are you looking to enter the formula in F10:F15 for example? You can simply use this structure:

Range("F10:F15").FormulaR1C1 = "=VLOOKUP(RC[-5],[fname]Hotel!C3:C26,5,FALSE)"

You shouldn't need to recalculate the cells/sheet/workbook.

 

[iainmartin100]

Hi all,

Many thanks for the quick replies,
The formulas are in cells F10:F40 & J10:J40

fname I am using for the prompt to choose the external excel file (that part works fine).

I need to use the full calc as the spreadsheet is large and I had to turn off the auto calc.

Many thanks

 

[iainmartin100]

Hi Richard,
I tried using the code you stated and put the range to F10:J40 but I get N/A everywhere. It looks like the macro copies the same formula across all cells?

 

[Norie]

How exactly are you putting the filename in the formula?

 

[iainmartin100]

When using fname it brings up a dialog box [Update Values:fname] the user chooses the file and that part seem to work ok,
My issue now is the ranges, the first range is F10:F40 which all seem to update correctly, the second range is J10:J40 but the macro looks like it changes the original formula so I get N/A's

 

[Norie]

Iain

Is this a built in dialog box?

Or is it something that the code is doing?

 

[iainmartin100]

Hi Norie,
Thanks for your interest in this one

The dialog box is built in, im just using standard features

The only formulas used are in the cells F10:F40 which are =VLOOKUP(A10,[fname]Hotel!$C:$Z,5,FALSE) [A10 goes up by 1 each time]
And in cells J10:J40 =VLOOKUP(A10,[fname]Hotel!$C:$AB,26,FALSE) [A10 goes up by 1 each time]
If I click enter in the formula bar within one of these cells it brings up the dialog box so im guessing the marco really only needs to force excel to do the calculations.