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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top