Macro to Find all values in a column and return the adjacent columns Info

Jmhardy

New Member
Joined
Jul 16, 2014
Messages
2
I have this example data...

A. B. C.
Topeka SE-6/9/20156/9/2015 Topeka SE
Man Spa-6/9/20156/9/2015 Man Spa
Manhattan West-6/9/20156/9/2015 Manhattan West
Mcpherson-6/9/20156/9/2015 Mcpherson
Emporia-6/9/20156/9/2015 Emporia
Topeka North-6/9/20156/9/2015 Topeka North
South Lawrence-6/9/20156/9/2015 South Lawrence
Manhattan East-6/9/20156/9/2015 Manhattan East
Leavenworth-6/9/20156/9/2015 Leavenworth
West Central-6/7/20156/7/2015 West Central
St. Joe-6/9/20156/9/2015 St. Joe
West Central-6/9/20156/9/2015 West Central

<colgroup><col><col><col></colgroup><tbody>
</tbody>

What i need is a macro that can find all of a particular location (name) from column c then return the date in a message box. from a certain time frame. Example would be the user would want to look up west central from 6/7-6/9 and return the dates that data was entered into this database in a message box. So this query's result would be 6/7
6/9
which represents that 6/8 was not recorded. If the macro could then message box the user that 6/8 was missing that would be helpful but not necessary

I may also in the future want it to return other column's data

The user will access this by clicking a button then will be prompted to put in the date range and club name. Then the results will be presented.

The data they are accessing is in another workbook
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You can assign this to a button by clicking 'Developer' on the Ribbon, then click 'Insert', choose the button from the Forms Control tool box.
Code:
Sub getDates()
Dim wb2 As Workbook, sh As Worksheet, lr As Long, c As Range, itm As String
Set wb2 = Workbooks(2) 'Edit workbook name, assumes workbook is openSet 
sh = wb2.Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
itm = InputBox("Enter a string to search for.", "SEARCH ITEM")    
    For Each c In Range("C2:C" & lr)        
        If c.Value = itm Then            
            dt = dt & c.Offset(0, -1).Value & ", "        
        End If    
    Next
MsgBox "Dates for selected item are " & Left(dt, Len(dt) - 2) & "."
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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