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?
JustinFinancial ControlAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window

0
JustinFinancial ControlAuthor 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
NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JustinFinancial ControlAuthor 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
NorieAnalyst Assistant Commented:
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
JustinFinancial ControlAuthor Commented:
I am getting the error message "Object variable or With block variable not set"
0
JustinFinancial ControlAuthor Commented:
Please advise
0
NorieAnalyst Assistant Commented:
Can you post the exact code you are using?
0
JustinFinancial ControlAuthor 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
JustinFinancial ControlAuthor Commented:
Please advise.
0
NorieAnalyst Assistant Commented:
Where are you getting the error?
0
JustinFinancial ControlAuthor Commented:
I am not at work so I cannot see but does it work in your Excel?
0
NorieAnalyst Assistant Commented:
Yes, I tested it before I posted it.
0
JustinFinancial ControlAuthor 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
JustinFinancial ControlAuthor Commented:
Please advise?
0
JustinFinancial ControlAuthor Commented:
Superb!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.