Drewster66

New Member
Joined
Jun 3, 2019
Messages
6
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,017
Office Version
  1. 365
Platform
  1. Windows
Try this. You'll have to set your ranges manually. e.g. range("A1:C100")

Code:
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

New Member
Joined
Jun 3, 2019
Messages
6
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,017
Office Version
  1. 365
Platform
  1. Windows
could you post your updated code?
 

Drewster66

New Member
Joined
Jun 3, 2019
Messages
6

ADVERTISEMENT

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

New Member
Joined
Jun 3, 2019
Messages
6
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

New Member
Joined
Jun 3, 2019
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top