Vlookup error

grapevine

Board Regular
Joined
May 23, 2007
Messages
208
Could someone please help me. I have been trying to solve this for ages and I am sure it is something very stupid, but I cannot see what is wrong.
this code is part of a much large macro. The active workbook is dn (i should not really need to activate it as the rest of the sheet is populating but i was getting desperate and clutching at straws.
I have a list of information which starts in row 11. I am entering an address for the record based on an order number in Cell A11. The order number in the spreadsheet starts with 00 and in the past I have just multiplied by 1 to get rid of them and it did work. The address is stored in a workbook which is referenced by the code AD and is on Sheet1 with a range named addresses.

When i hover over the ad - i get the correct workbook name
when i hover over addresses i get the correct range of R1C1:R14C14
when i hover over the formulaR1C1 i get ""

With this code i get a 438 error
Code:
Windows(dn).Activate 'i put this in to ensure it was looking up from the correct workbook - just in case

 'Address information

    Range("A1").FormulaR1C1 = "=VLOOKUP(R[10]C*1,'[" & ad & "]" & Sheet1 & "'!" & addresses & ",4,0)"
    Range("A2").FormulaR1C1 = "=VLOOKUP(R[9]C*1,'[" & ad & "]" & Sheet1 & "'!" & addresses & ",5,0)"
I have also tried the code below and get a 1004 error
Code:
Range("A2").Formula = "=VLOOKUP(R[10]C*1,'[" & ad & "]'!" & addresses & ",5,0)"
If someone can point out the error of my ways I would be really really grateful
Many thanks
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

grapevine

Board Regular
Joined
May 23, 2007
Messages
208
Managed to sort it
Code:
Range("A1").Formula = "=VLOOKUP(R[10]C*1,'" & ad & "'!" & addresses & ",4,0)"
I used the square brackets in an older version of excel so perhaps that it now out of date with the new xlsx
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,332
Messages
5,467,987
Members
406,562
Latest member
Isbenji

This Week's Hot Topics

Top