Reverse Lookup

[Drewster66]

I really enjoyed your tutorial on Excel Reverse lookup (https://youtu.be/mIht6gICsS4) but Im wanting to convert it from a function to a Sub Routine and getting HEAPS of erros {its obvioulsy not just a matter of removeing Function() and replacing with Sub()}.

Is anyone able to assist with this at all, Thank you in advance.

I have posted the working code below:

Function RL(TruckValue As Range, LooKupTable As Range)
HRow = LooKupTable.Rows(1).Row - 1
HCol = LooKupTable.Columns(1).Column - 1

RL = ""

For Each cell In LooKupTable
If cell.Value = TruckValue.Value Then
RL = RL & Cells(HRow, cell.Column).Value
End If
Next cell
End Function

 

[lrobbo314]

Try this. You'll have to set your ranges manually. e.g. range("A1:C100")

Sub RL()
Dim TruckValue As Range: Set TruckValue = Range("yourrange")
Dim LooKupTable As Range: Set LooKupTable = Range("anotherRange")
HRow = LooKupTable.Rows(1).Row - 1
HCol = LooKupTable.Columns(1).Column - 1

RL = ""

For Each cell In LooKupTable
If cell.Value = TruckValue.Value Then
    RL = RL & Cells(HRow, cell.Column).Value
End If
Next cell
End Sub

 

[Drewster66]

lrobbo314, Thank you SO much for taking the time to reply to my question, I am extremely grateful.

I am, however, getting a Runtime error 13 - Type mismatch on
If cell.Value = TruckValue.Value Then

Again thank you for your help and super fast response.

 

[lrobbo314]

could you post your updated code?

 

[Drewster66]

Thank you.

Sub RevLook()
Dim TruckValue As Range: Set TruckValue = Range("E8:E21")
Dim LooKupTable As Range: Set LooKupTable = Range("B2:D5")

Dim RL As String
HRow = LooKupTable.Rows(1).Row - 1
HCol = LooKupTable.Columns(1).Column - 1

RL = ""

For Each Cell In LooKupTable
If Cell.Value = TruckValue.Value Then
RL = RL & Cells(HRow, Cell.Column).Value
End If
Next Cell
End Sub

 

[Drewster66]

I have tried Diming cell as Range and as String, but neither seems to work.

I can see that cell.value is assigned the value of "Truck1" just before the error which makes me think it should be a string, but as I said, that doesn't work either.

 

[Drewster66]

lrobbo314, I believe I have worked it out.

It would appear I set the TruckValue to a number of cells, rather than an individual cell.

I also assigned cells to a variable called "CV" as well as TruckValue to "TV", this helped me compare the values and eliminated the error message.

Lastly, I assigned the result "RL" to Range("F8").

Again, lrobbo314, THANK YOU for your invaluable help and of course thank you to MrExcel for the video in the first place.

Completed working code.
-----------------------------------------------------------------
Sub RevLook()

Dim TruckValue As Range: Set TruckValue = Range("E8")
Dim LooKupTable As Range: Set LooKupTable = Range("B2:D5")
Dim TV As String
Dim RL As String
HRow = LooKupTable.Rows(1).Row - 1
HCol = LooKupTable.Columns(1).Column - 1

'RL = ""

For Each cell In LooKupTable
TV = TruckValue
cv = cell.Value
'If cell.Value = TruckValue.Value Then
If cv = TV Then
RL = Cells(HRow, cell.Column).Value
Range("F8").Value = RL
End If
Next cell
End Sub