Create & search through named range & export match to new cell?

[rlee16]

In the below code, I have the result from a combobox being compared to a range and if there is a match, to place the row number into another cell. I then convert this number into the name of a company.

As I am continuously adding new companies to the list, I would like my program to become more flexible. Perhaps using a name for the range would be better, but I am confused as to how to do this (since I am only a novice). Also, it there a way to directly put the name of the company into a cell as opposed to the number of the row?

Sub COMPANY_INFO()
Dim C As Integer
Dim i As Integer

For C = 260 To 398
If cmb_Menu2.Value = CStr(Worksheets("Data&quot.Cells(C, 2).Value) Then
i = C
Worksheets("Data&quot.Range("CompaniesControl&quot.Value = i ''''''''''''''''''''''''
End If
Next

End Sub

 

[RobBroekhuis]

Sounds like you could use VLOOKUP for this purpose. For example, if the company name is in the column next to the one you're looking in, you could use:

Function LookMeUp(r As Range, s As String) As String
Dim x
On Error Resume Next
x = Application.WorksheetFunction.VLookup(s, r, 2, False)
If Err <> 0 Then
LookMeUp = &quot;&quot;
Else
LookMeUp = x
End If
End Function

You could then call the function as follows:

set LookUpRange=range(&quot;B260:B398&quot
Worksheets(&quot;Data&quot.Range(&quot;CompaniesControl&quot=lookmeup(LookUpRange,cmb_Menu2)

Rob

 

[rlee16]

Thanks Rob,

I'm still trying to decipher your code, but I used the following to solve my problem (which was more focused on the range being the variable, rather than the variable being looked up). Instead of defining the entire range, I defined the start of the range and the end of the range.

Sub COMPANY_INFO()
Dim C As Integer
Dim i As Integer
Dim x As Integer
Dim f As Integer


x = Worksheets(&quot;Data&quot.Range(&quot;coStart1&quot.Row
f = Worksheets(&quot;Data&quot.Range(&quot;coFinish1&quot.Row

For C = x To f
If cmb_Menu2.Value = CStr(Worksheets(&quot;Data&quot.Cells(C, 2).Value) Then
i = C
Worksheets(&quot;Data&quot.Range(&quot;CompaniesControl&quot.Value = i ''''''''''''''''''''''''
End If
Next

End Sub


Looking at your code again, I see that you are using a Function to replace the Vlookup in Excel. If I would like to add in a variable for the column row to lookup (in this case, instead of &quot;2&quot;, I would like a variable that I can control&quot, how would I add that in?

Thanks in advance Rob!

Richard

 

[RobBroekhuis]

Function LookMeUp(r As Range, s As String, ColNr as integer) As String
Dim x, r1 as range
set r1=range(r1,r1.offset(0,ColNr))
On Error Resume Next
x = Application.WorksheetFunction.VLookup(s, r1, ColNr, False)
If Err <> 0 Then
LookMeUp = &quot;&quot;
Else
LookMeUp = x
End If
End Function

This one adds the column number as an additional parameter to the function. I also changed the function so that you only need to specify the range to be searched - that range is automatically expanded to include the column from which you want to return the value.
You're best off naming your range (manually or programmatically, whichever makes most sense for you). For instance, if you name the range &quot;companies&quot;, then you can call the function as

Worksheets(&quot;Data&quot.Range(&quot;CompaniesControl&quot = LookMeUp(range(&quot;companies&quot,cmb_menu2,2)

Rob