Custom VLOOKUP - setting range

[Eadam]

I'm currently having an issue when trying to define a range in VBA.

The way the code works is that it prompts a user to select two files. These are a full list of products and then a descriptions file (which should populate the full list with relevant descriptions)

The code which I can't get past at the moment is:

ActiveCell.Formula = "=VLOOKUP (C[-1], '[" + Descriptions_file + "]" + Descriptions_sheet + "'!R1:R1048576,4,FALSE)"

Sorry if this seems simple, I'm a complete novice!

The range I would like to select in the Descriptions_sheet is the whole sheet. Though I have to keep this sheet name generic as there are multiple sheets in the workbook.

Cheers.

 

[Marc L]

Hi !

First, better use & operator for text concatenation instead of + …

Second, your formula seems to be in R1C1 mode so try .FormulaR1C1 !

And why a formula by code ?
​

 

[Stevie]

Hiya Eadam!
Do you mean:

ActiveCell.Formula = "=VLOOKUP(A1,[" & Descriptions_file & "]" & Descriptions_sheet & "!" & Range(Cells.Address) & ",4,FALSE)"

In this ^ you need to replace A1 with the cell that contains what you are looking up as I wasn't sure what you meant by C[-1], this can be dynamic as well if needed.

Range(cells.address) is just a valid range for the whole sheet. I imagine you don't actually need the whole sheet, Range("A1").CurrentRegion or activesheet.usedrange may be more appropriate.

If you do want to use R1C1 notation, then like Marc said you need to use activecell.formulaR1C1 = and put all your range references in R1C1 format.