Find specific records in a table

Question

I am looking to find only certain records and handle them accordingly. I have a table called Locations and one of the columns is labelled Location. Currently my code handles ALL locations when I run the macro; however, I would like to write a variation of the macro that runs only certain locations (a range). The location values are all 2 letters followed by 5 numbers. Example: CA10020

What I want to do is prompt for the starting and ending location values and then process only those ones. Example: CA10001 to CA13240 Everything before and after those values would be ignored... but those values AND everything between them would be handled.

Normally I have a good idea where to start and through trial and error I can figure out the rest. In this case I am stumped on where to begin. As you can tell, I am not a VBA expert.

I currently have this code which handles ALL the records which is not what I need to do now:

For i = 1 To Range("Locations").Rows.Count
    Range("F3").Value = Range("Locations[Location]")(i)
    'I have other code here that handles the new value of cell F3
Next i

UPDATE: I added this code:

Dim Start_Location As Variant
Dim End_Location As Variant
Start_Location = InputBox("What is the starting location?")
End_Location = InputBox("What is the ending location?")

...but I have no idea how to handle those values now.

How will you be passing in your Start and End locations. I.E., how will you tell the macro to look at `CA10001 to CA13240`? Input Box with text? Input Box with Range Selector? Data validation? Will the values be in some static cell? — urdearboy, Aug 21 '18 at 15:10
Have a look here for how to select different parts of a table: https://stackoverflow.com/questions/43541445/select-entire-column-in-table-using-excel-vba/43541508#43541508 — Pᴇʜ, Aug 21 '18 at 15:11
Self promotion eh @Pᴇʜ ;). Only joking. Good solution, I up ticked — urdearboy, Aug 21 '18 at 15:13
@Pᴇʜ - That shows me how to select certain parts of the table but not how to filter certain values. — G-J, Aug 21 '18 at 15:17
You can use the [WorksheetFunction.Match Method](https://learn.microsoft.com/en-us/office/vba/api/Excel.WorksheetFunction.Match) to find the row number of user input `Start_Location` and `End_Location` and then run your `For` loop from/to these row numbers. — Pᴇʜ, Aug 21 '18 at 15:22
I was having trouble Google'n what to do prior to posting but kept searching and was able to come up with the answer I just posted. Not sure if it is the cleanest or most efficient way to do it but it works — G-J, Aug 21 '18 at 15:27

score 0 · Answer 1 · answered Aug 21 '18 at 15:26

0

Dim Start_Location As Variant
Dim End_Location As Variant
Start_Location = InputBox("What is the starting location?")
End_Location = InputBox("What is the ending location?")

For i = 1 To Range("Locations").Rows.Count
    If Range("Locations[Location]")(i) >= Start_Location And Range("Locations[Location]")(i) <= End_Location Then

        Range("F3").Value = Range("Locations[Location]")(i)

        'Code to handle the new value of cell F3
    End If

Next i

answered Aug 21 '18 at 15:26

G-J

1,080
2
16
32

The issue here is that the `For` loop still runs through *all* rows in the range. You can reduce the runtime by running through rows between start and end location only. See my answer. – Pᴇʜ Aug 22 '18 at 10:03

Pᴇʜ · Answer 2 · 2018-08-23T09:29:21.647

I suggest to find the first and last location using Match() to reduce the runtime of the loop.

Option Explicit

Public Sub ProcessLocationFromTo()
    Dim FirstLocation As Variant
    Dim LastLocation As Variant
    FirstLocation = InputBox("What is the starting location?")
    LastLocation = InputBox("What is the ending location?")

    Dim RngLocation As Range
    Set RngLocation = Range("Locations[Location]")

    Dim FirstRow As Long
    On Error GoTo ERR_FIND_LOCATION
    FirstRow = Application.WorksheetFunction.Match(FirstLocation, RngLocation, 1)
    On Error GoTo 0

    Dim LastRow As Long
    On Error GoTo ERR_FIND_LOCATION
    LastRow = Application.WorksheetFunction.Match(LastLocation, RngLocation, 1)
    On Error GoTo 0

    Dim i As Long
    For i = FirstRow To LastRow
        Range("F3").Value = RngLocation(i)

        'Code to handle the new value of cell F3
    Next i


    Exit Sub

ERR_FIND_LOCATION:
    MsgBox "The location was out of range." & vbCrLf & "It must be between """ & RngLocation(1) & """ and """ & RngLocation(RngLocation.Count) & """."
    Exit Sub
End Sub

Note that this only works if the table is sorted by location! Otherwise you will need to sort by location before running the procedure:

With ListObjects("Locations").Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=Range("Locations[[#All],[Location]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Find specific records in a table

2 Answers2