Lookup

Posted by Raj on July 10, 2001 11:06 PM

Hi,
I am not sure if this question has been posted ( is there an easier way of searching for a question that is already there ? ) but I desperately need to do this using VBA.

Assuming I have a range of numbers in A1:A10 ( eg, 1,2,3,4,5,6,7,8,9,10 ) and another range of numbers in B1:B10 ( eg. 34,6,9,121,789,545,77,10,89,100).Starting from the cell A1 ( values is 1 ) , I want to cross-reference the range B1:B10 and if there is a match to store the results in C1 onwards. So in this case, the results should return the values '6,9,10'. The results must be stored in cell C1 onwards ( C1,C2,C3...)

Is this possible ?

Appreaciate your help.

Thanks.

Posted by Malc on July 11, 2001 2:46 AM

Private FirstRowNum as Integer, FirstNumber as Integer
Private SecondRowNum as Integer, SecondNumber as Integer
Private ThirdRowNum as Integer

Sub GetNumber()
For FirstRowNum = 1 to 10
FirstNumber = cells(FirstRowNum,1)
For SecondRowNum = 1 to 10
SecondNumber = cells(SecondRowNum,2)
if FirstNumber = SecondNumber than AddNum
Next SecondRowNum
Next FirstRowNum
End Sub

Sub AddNum
ThirdRowNum = ThirdRowNum +1
Cells(ThirdRowNum,2)=FirstNumber
End Sub

This will work on all 65,000 rows (use long integers) but will be slow suggest you try using Multidemsioned arrays if there is more than a thousand or so rows

Posted by Raj on July 11, 2001 7:01 AM

Thanks a lot Malc. Great help you are.

Cheers Mate