How to define a vlookup range using R1C1refrence style in VB code

Hi Guys, I am doing a macro and I want to copy and paste append  down column D in an Excel tab and inserts a lookup formula where it concatenates the values 3 cells left and 2 cells left of the Cursor thus

=VLOOKUP(RC[-3]&RC[-2]. The range I want to look up  is 3 columns to the right (column G) , 15 rows down and finishes in column I.

For example, my cursor is at Range ("D648"), my formula will be =vlookup (RC[-3]&RC[-2], R648C7:R662C9,2,False). How do I make this lookup range variable in VB code?
JCutcliffeAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Not sure I follow.

Do you want to find the next empty row in column D and then put the formula I posted in the next 10 rows down?

If you do then try this.
Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(10).FormulaR1C1 = "=VLOOKUP(RC[-3]&RC[-2], RC[3]:R[15]C[4], 2,0)"

Open in new window

0
 
NorieVBA ExpertCommented:
Try this
Range("D648").FormulaR1C1 = "=VLOOKUP(RC[-3]&RC[-2], RC[3]:R[15]C[4], 2,0)"

Open in new window

0
 
JCutcliffeAuthor Commented:
Hi Norie, if I loop down Column D until it hits the first blank cell, then insert this formula in the first 10 cells down, what's the best code to use?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
JCutcliffeAuthor Commented:
Hi, that is correct. Except I am not copying and pasting so do I have  to do a Loop to get the formula  there?
0
 
NorieVBA ExpertCommented:
No the code I posted will find the next blank cell in column D and put the formula in the next 10 rows down.

For example, if the next empty row in D was 648 the formula would go in D648:D657.
0
 
JCutcliffeAuthor Commented:
I am getting the error message "Object variable or With block variable not set"
0
 
JCutcliffeAuthor Commented:
Please advise
0
 
NorieVBA ExpertCommented:
Can you post the exact code you are using?
0
 
JCutcliffeAuthor Commented:
Sub () Mapping

Sheets ("Mapping") .Activate

Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(10).FormulaR1C1 = "=VLOOKUP(RC[-3]&RC[-2], RC[3]:R[15]C[4], 2,0)"


End sub ()
0
 
JCutcliffeAuthor Commented:
Please advise.
0
 
NorieVBA ExpertCommented:
Where are you getting the error?
0
 
JCutcliffeAuthor Commented:
I am not at work so I cannot see but does it work in your Excel?
0
 
NorieVBA ExpertCommented:
Yes, I tested it before I posted it.
0
 
JCutcliffeAuthor Commented:
I am getting a Type Mismatch Error Message  on the RngDst1 bottom line of the code below. Any ideas?


Sub Macro30()
Dim wsDst1 As Worksheet

Dim wsSrc As Worksheet
Dim rngDst1 As Range
Dim rngSrc As Range
Dim arrSheets As Variant
Dim i As Long
Set wsDst1 = Sheets("Mapping")

Set rngDst1 = wsDst1.Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(10).FormulaR1C1 = "=VLOOKUP(RC[-3]&RC[-2], RC[3]:R[15]C[4], 2,0)"
0
 
JCutcliffeAuthor Commented:
Please advise?
0
 
JCutcliffeAuthor Commented:
Superb!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.