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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Stridhan

Well-known Member
Joined
Mar 5, 2014
Messages
568
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can also do this by formula:

=LOOKUP(1048577,SEARCH("rent",C:C),E:E)
 

AC

Board Regular
Joined
Mar 21, 2002
Messages
153

ADVERTISEMENT

Scott, All I get is #NUM! when I put in the formula, should this work with Excel 2003?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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)
 

AC

Board Regular
Joined
Mar 21, 2002
Messages
153
Scott, that worked with a range, what does the 1048577 do?
Thanks
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

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