FormulaR1C1 with VLOOKUP or HLOOKUP

K

krisna_wisnu

New Member
Joined
Mar 2, 2007
Messages
4
Kinda need help here.
I have this kind of code in my macro:

Code: 
Dim str as string
str = "1693B_cdd"
For j = 10 To 18
    Range("B" & j).FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & str & ".xls]Sheet1'!R12C10:R88C12,2,0)"
Next j

its working fine, but not the one that i wanted to be.
The problem is with the range, R12C10:R88C12, since my project need the range to be changed for every different file, so i need to put the 'range' value to a variable and i am trying to put the range value in a variable (which the variable value also changing as the file changed) but it wont works.
Anyone has idea??
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
What do you mean "it won't work"? You haven't actually posted what you tried, so don't know what you did wrong. This would work:
Code: 
Dim str as string, myaddr as String
str = "1693B_cdd" 
myaddr="R12C10:R88C12"
For j = 10 To 18 
    Range("B" & j).FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & str & ".xls]Sheet1'!"&myaddr&",2,0)" 
Next j
 
Upvote 0
Wow!
amazing it works!!!!!!

What I meant before it didn't work is that I tried many times already, but got result compiling error!
Now I know where is my mistakes:

Code: 
Dim str as string, myaddr as String
str = "1693B_cdd"
myaddr="R12C10:R88C12"
For j = 10 To 18
    Range("B" & j).FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & str & ".xls]Sheet1'!"& myaddr &",2,0)"
Next j

in between the variable "myaddr" need space!
Wuiihh..

Thanks Glenn,


w15nu
 
Upvote 0
Another questions:
Is there any way in
Code: 
FormulaR1C1
we can add a string? not from variable, but a string.

example:
The result of VLOOKUP from the FormulaR1C1 is 255,
and I want at Range("B" & j) to show : RAN255
The string "RAN" is always the same.
I did tried to put the string into a variable, and concatenate it like the code below

Code: 
Dim dcs as string
dcs = "RAN"
range("Q" & j).FormulaR1C1 = "=CONCATENATE(VLOOKUP(RC[-16],'[" & str & ".xls]Sheet1'!" & range2 & ",16,0)," & " dcs " & ")"

The result is not quiet the one I wanted: #NAME?
and showing the code :
Code: 
=CONCATENATE(VLOOKUP(A10,'D:\New_Site\CDD\[1693B_cdd.xls]Sheet1'!$H$9:$W$10,16,0), RAN )

Anyone has idea?
Thanks


w15nu
 
Upvote 0
Its done, wekeke
finally, I found it
thanks

Code: 
range("Q" & j).FormulaR1C1 = "=CONCATENATE(VLOOKUP(RC[-16],'[" & str & ".xls]Sheet1'!" & range2 & ",16,0),""RAN"")"
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Worksheet Change Event
Replies
2
Views
372
sspatriots
S
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
360
sspatriots
S
F
  • Question
How to get optionbutton values from two different groups
Replies
2
Views
236
NoSparks
N
H
  • Solved
Macro to display message end of Code where Col D contains blanks
Replies
7
Views
330
howard
H
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
318
sspatriots
S

Forum statistics

Threads
1,214,667
Messages
6,120,821
Members
448,990
Latest member
rohitsomani

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top