Range intersection

  • Thread starter Thread starter Riddler
  • Start date Start date
R

Riddler

I have a database set up in Excel 2003 to track RFQ numbers and info
for each. I would like to use named ranges to store and retrieve info
for each entry. The list below is a simplified version of my database.
I have range names for each column but no range names for the rows. How
can I refer to the "Supplier" of ID# RFQ1B ?
I was using
a=range("RFQ1B Supplier").value
to return the intersection range value but it only works when each row
has a range name(like "RFQ1B"). This database will continue to grow in
length and I would like to not have to create range names for every row
although it does work. I woudl also like to not have to just loop down
the first column until I found a match and return the row number. I was
looking at the database query a bit but I am not sure how to set that
up and if it would be robust enough for a ever growing list.



ID RFQ Rev Supplier
RFQ1A 1 A XYZ
RFQ1B 1 B ABC
RFQ2A 2 A XYZ
RFQ3A 3 A NOP
RFQ3B 3 B XYZ
RFQ4A 4 A ABC


Thanks for any help I can get.
Scott
 
You can use the find method. The question is do you have lparts that are
supplied by more than one supplier? If so then how do you wnat to handle
that? This code does not handle that...

dim rng as range

set rng = sheet1.Columns(1).Find(What:="RFQ1B", _
LookAt:=xlWhole, _
LookIn:=xlformulas, _
MatchCase:=false)
if rng is nothing then
msgbox "sorry, that does not exist"
else
msgbox intersect(rng.entirerow, range("MyColumn")).Value
end if
 
Here is the code I tested from what you sent. The rng keeps coming back
as nothing. I even cut and pasted the text I am serching for from the
spread sheet to the code to make sure it was correct. Is the Lookin
correct? I tried xlText and xlValue but neither worked.
Thanks
Scott

Sub test()

Dim rng As Range
Set rng = Sheets("Master log").Columns(1).Find(What:="RFQ1A", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rng Is Nothing Then
MsgBox "sorry, that does not exist"
Else
MsgBox Intersect(rng.EntireRow, Range("Rev")).Value
End If

End Sub
 
I figured out why the code was not working. I had created the "RFQ1A"
that showed in the cell with a formula. I converted the RFQ#'a to text
and it worked fine.

Thanks much for the help.

Scott
 
Back
Top