Vlookup and VBA

Microsoft Excel 2003 (full product)
January 28, 2010 at 09:41:02
Specs: Windows XP
Hey there. This is one part in a series of questions I'll be asking here as I attempt conquer our monthly technology bill. See my first question here:


Now I'm moving on to including a macro that does vlookup. I've already recorded the first portion, so I have the code for that. What I'd like to know is how can I write the code that will allow me to apply that formula to the entire column.
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(B:B, apples,2, FALSE)), "" "", VLOOKUP(B:B],apples,2,FALSE))

"apples" denotes the name of an array.

Here is the 2nd wrench in the problem. The Vlookup currently looks in column B and return a value in Column A. However, if there is a value in Column C, I would like for the macro to look there instead and return a value in A.

So let's say I had this data set:

3 2

and an array where

1 = apples
2 = bananas
3= oranges

I would like the final product to look like


Is it possible? Can you help?

See More: Vlookup and VBA

January 28, 2010 at 12:18:08
Do you have a range named "A"? If not, this line won't work:


If you are trying to select and entire column, you would use:


For a smaller range, you might use Range("A1:A100").Select

BTW - you don't have to select a range to perform an action on it. The recorder adds a lot of fluff that should be cleaned up later.

Range("A1:A100").Formula = "....." works just as well and is more efficient.

In addition, your code for the formula won't work.

You have a typo - the ] after the second B:B

In addition, FormulaR1C1 won't work with the B:B reference.

The syntax would have to be one of these 2:

FormulaR1C1 = "=VLOOKUP(C[1],apples,2,0)"


Formula = "=VLOOKUP(B:B,apples,2,0)"

Finally, why do you want to apply this formula to an entire column? Is your dataset really that big?

Before we turn the 2nd wrench (re: Column C) please clarify the other issues.

Report •

January 29, 2010 at 08:35:02
I'm not sure what you meant by your first question. I do have an array named "apples." The first column is the number, and the second column is the fruit.

My dataset, the monthly technology bill, is about 20,000 rows long. About 500 rows have changes- meaning that for 500 rows, I would need my macro to look in "Column C" rather than Column B. For the other 14,500 rows, the macro should focus on column B.

Report •

January 29, 2010 at 09:40:37
re: I'm not sure what you meant by your first question

The first line of the code you posted was:


Unless you have a range named A in your workbook, the code is going to fail as soon as it gets to that line. VBA will not recognize "A" as a valid range reference unless there is range with that name in the workbook.

Range("apple").Select will select the range named apple
Range("A1").Select will select A1
Range("A:A").Select will select Column A
Range("A1:A100").Select will select A1:A100

Range("A").Select is going to look at the collection of Named Ranges in the workbook and if it doesn't find one named A, it's going to throw up an error.

That's why I asked if you had a range named "A" in the workbook. I was trying to understand why you were selecting that range as it didn't seem to relate to the rest of your question.

Report •

Related Solutions

January 29, 2010 at 11:29:23
Ok, then the first line of my code is wrong. There is no range named "A". I only have an array named "apples."

I wanted to select the first cell in Column A. Then apply the formula to the entire column. So will I have to write a code for a "Paste Special"?

Report •

January 29, 2010 at 12:07:44
First, you need to fix the other errors in your code:

1 - The use of .FormulaR1C1 when your formula is not of the R1C1 type.
2 - The typo of the extra "]" in the second VLOOKUP

In other words, from a purely VBA perspective, neither of the 2 lines you posted were going to acheive your goal, they were simply going to throw up VBA related errors.

I believe the code you are looking is shown below, but I'm still curious about why you need to fill an entire column. I see you are using Excel 2003. Do you really need to put this formula in 65,536 cells? If you don't have 65,536 rows of data in Column B, then you are making your spreadsheet bigger and less efficient than it needs to be.

Every time the sheet recalculates, it's going to recalculate every one of those 65,536 formulae. Is that really needed?

Sub FillColumnWithFormula()
Range("A:A").Formula = _
   "=IF(ISNA(VLOOKUP(B:B,apples,2,0)), """", VLOOKUP(B:B,apples,2,0))"
End Sub

Report •

Ask Question