Use of Ranges from VBA.

[Wulluby]

Any quick pointers on what I am missing here would be greatly appreciated.


I have dimmed a range with

Dim rngRole As Range


Filled the range

[pre][code]Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Set rngRole = Selection

And now trying to populate a column with a vlookup that uses this range

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Range("A1&#34.Select
ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC6," & rngRole.Value & ",1,FALSE)),RC6,"""&#34"
ActiveCell.Offset(1, -1).Range("A1&#34.Select
Loop[/code][/pre]
I have tried different variations from

"=IF(ISNA(VLOOKUP(RC6,rngRole,1,FALSE)),RC6,"""")"

"=IF(ISNA(VLOOKUP(RC6,rngRole.value,1,FALSE)),RC6,"""")"

"=IF(ISNA(VLOOKUP(RC6," & rngRole & ",1,FALSE)),RC6,"""")"

"=IF(ISNA(VLOOKUP(RC6," & rngRole.Value & ",1,FALSE)),RC6,"""")"


I seem to be unable to use the value I assume to be within rngRole in the VLOOKUP.


Thanks in advance

 

[Hui]

rngrole is a Range set to $A$1:$A$10 (if you have 10 values in Column A)


What Range are you trying to define for the VLookup ?

 

[NARAYANK991]

Hi ,


The address for rngRole has also to be specified in R1C1 format ; try this :

[pre]

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[6]," & rngRole.Address(, , xlR1C1) & ",1,FALSE)),RC[6],"""")"
ActiveCell.Offset(1, 0).Select
Loop

[/pre]
Narayan

 

[Wulluby]

Thanks for the responses, both responses have come back with results.


Narayank, the [] round the 6es seemed to throw that off but when removed it fixed it, RC6 was looking at column M instead of F.


I found that the formula was looking at the wrong range though, which brought up your question Hui. What range am I trying to define, in this case the wrong one. Wrong range in was an error code 18, problem was 18 inches in front of the screen.


The one I was trying to define is a variable range from user to user and rather than scan hundereds of empty rows to be on the safe side I thought this might be more efficient.


Thank you both.