Needing a relative cell reference for a vba formula insertion

**timsilver** · 02-07-2013, 01:46 PM

Hi Guys,

I have the following code:

Cells(erow, 43).Formula = "=IF(ISNA(VLOOKUP(AY2,Sheet4!$A$1:$B$1000,2,FALSE)),"""",VLOOKUP(AY2,Sheet4!$A$1:$B$1000,2,FALSE))"

What this does as part of a VBA userform is a VLOOKUP using a cell that contains merged data from fields of the form This form is used for adding new products to a spreadsheet and as it is completed and the products saved via the userform, the sheet is populated in the background and when the next product is added it goes on to the next empty row

. This is why there are the erow references within there.

The VLOOKUP itself returns a value of nothing if there is no data present which is why it is presented as an IF function.

The sample of code works though what I am having trouble with is that the code that I insert has a lookup reference to cell AY2 and what I need is to reference column AY but I need the row to be relative to the line that it is entered on. Am I able to use wildcards in any way to get what I am after?

Any help that you can offer would be greatly appreciated.

Thanks

**MrShorty** · 02-07-2013, 01:59 PM

Maybe it is just because I have become used to using R1C1 notation, but I think the easiest might be to use R1C1 notation (http://www.bettersolutions.com/excel...I416010881.htm) and the formular1c1 property (http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx). In R1C1 notation, an R without further description means "current row" so, a reference to RC2 refers to column 2 of the current row (R[-1]C2 refers to column 2 one row above and R[1]C2 refers to colummn 2 one row down). Column AY is column 51, isn't it?

**timsilver** · 02-07-2013, 02:04 PM

Thanks for the reply, yes it is column 51 and this is going in on column 43, so RC8 should would. I will test this now.

**MrShorty** · 02-07-2013, 02:17 PM

If you are going to use a relative column reference, enclose it in brackets RC[8]. RC8 without brackets refers to the current row in column 8. RC[8] refers to the current row 8 columns to the right.