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: http://www.computing.net/answers/of...

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.

Range("A").Select

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:

B C

1

2

3 2and an array where

1 = apples

2 = bananas

3= orangesI would like the final product to look like

A

apples

bananas

bananasIs it possible? Can you help?

Do you have a range named "A"? If not, this line won't work: Range("A").Select

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

Range("A:A").Select

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)"

or

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.

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.

re: I'm not sure what you meant by your first questionThe first line of the code you posted was:

Range("A").Select

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").Selectwill select the range named appleRange("A1").Selectwill select A1Range("A:A").Selectwill select Column ARange("A1:A100").Selectwill select A1:A100

Range("A").Selectis 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.

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"?

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 VLOOKUPIn 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

Ask Your Question

Weekly Poll