VBA to find word in one column and get value from another column

AC

Board Regular
Joined
Mar 21, 2002
Messages
153
I need a macro that will look for the word Rent in column C, from the bottom up, and when it finds it will display the value in column E of the same row in a message box.
So if I had rent in C25 and rent in C36 it would show the value for cell E36 in the message box.
This is for Excel 2003
Thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
try
Code:
Sub lastRent()

Dim lr As Integer, x As Integer
lr = Cells(Rows.Count, "C").End(xlUp).Row
For x = lr To 1 Step -1
    If LCase(Cells(x, "C").Value) = "rent" Then
        MsgBox "Value for last rent is " & Cells(x, "E").Value
        Exit Sub
    End If
Next x
End Sub
 
Upvote 0
Scott, All I get is #NUM! when I put in the formula, should this work with Excel 2003?
 
Upvote 0
Scott, All I get is #NUM! when I put in the formula, should this work with Excel 2003?

See if it works with a range:

=LOOKUP(1048577,SEARCH("rent",C1:C1000),E1:E1000)
 
Upvote 0
Scott, that worked with a range, what does the 1048577 do?
Thanks
 
Upvote 0
It's 1 number higher than the number of rows in any version of Excel. If you put a numerical value in LOOKUP higher than any value that will occur in the array, it will return the last number in the array.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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