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.

 

[Norie]

I don't think I've ever seen such a dialog box, not even in Excel 2010.

Are you perhaps referring to the file selection dialog box that pops up because Excel doesn't recognise 'fname' as the name of a file?

Have you considered using the code to ask the user to select the file they want?

Then substituting fname with the name of the file they select.

That's kind of what I thought you were doing.

 

[iainmartin100]

Hi ya,

Yep you have what im attempting, fname is basicly forcing the user to choose a name as excel doesnt recognise it.
The selected files have ever changing names although the tab name inside remains constant.
Do you know of a better way to do what im attempting.

In basic:-
I have a number of vlookup formula's which need to refrance a 2nd workbook (of the users choice), all refrances need to be in the current workbook as the 2nd one is a system output/download. The 2nd file should be refranced but not opened phisiclly on screen.

Many thanks

 

[Norie]

Iain

I don't know a 'better' way but I here's code to display a dialog for the user before the formula goes on the worksheet.

It has no real error checking, just checks the user has selected a worksheet and has only been lightly tested.

I also had to make a slight change to the formula.

Anywhere here it is:

Dim fname As Variant
Dim strFormula As String
    fname = Application.GetOpenFilename("Excel Files (*.xls;*.xlsx;*.xlsm), *.xls?")
    If TypeName(fname) = "String" Then
        strFormula = "=VLOOKUP(RC[-5],'[fname]Hotel'!C3:C26,5,FALSE)"
        strFormula = Replace(strFormula, "fname", Dir(fname))
        Range("F10:F15").FormulaR1C1 = strFormula
    End If

 

[iainmartin100]

Hi Norie,

Unfortunatly this changes the fname part of the actual formula to be the spreadsheet name, if the document is saved then the macro wont work next time round as fname wont exist in the cells containg the formula.

 

[Norie]

How exactly is the user entering these formulas?

Are they just going to click in the formula bar whenever they need to change them?

 

[iainmartin100]

Within a tab (ranges F10:F40 & J10:J40) there are the formulas all very simular to =VLOOKUP(A10,[fname]Hotel!$C:$Z,5,FALSE) - 60 formulas in total.
The idea is that the user runs a macro which simply updates the values in all these 60 cell.
If the user wanted to do this manually they would have to click into each cell and select the file (60 times), so the hope was to be able to run a macro which remembers the user selected file and updates all 60 cells, though the next time the macro is run the same question needs to be asked.

I hope this helps, sorry for all the confusion
This issue is causing me so many headaches as I have no idea how to correct it.

 

[Norie]

Whenever the code I posted us run the user will be asked to select a file.

Then the current formulas will be replaced with new formulas, with the workbook reference(s) being the one they selected.

 

[iainmartin100]

The only issue is that i cant have the whole formula being replaced, is there a way your marco can just change fname to be the selected filename and leave the rest as it is?

 

[Norie]

Why not?

The whole formula isn't really being replaced anyway, it's the same formula with a different workbook reference.

Which is what you would get if you just did Find/Replace or similar on the formulas.

 

[iainmartin100]

Good point, I'll try and amend the formula's to all be the same.
The only differing part was tha I had some if statements in some cells to do with blanks but I could put that on all cells.
Many thanks for all your help on this