CODEWITHSUNDEEP
excelvba
Adrian Hudson
Adrian Hudson

Reputation: 31

Finding A Matching String with VLookup then Return Columns at Row Index

Building off an earlier StackOverflow question I posted: Indexing by ROW on a Column and finding partial matches in a string

I am creating a variation of this but where the string matches - Copy the entire row (A1:C1) for example then insert at the next empty row spot in a new sheet. 

Sub PeopleFilter()
' Defining Workbook and Worksheets to be used
Dim PeopleWorkbook As Workbook
Dim People As Worksheet
Dim UnitedKingdom, UnitedStates, Canada As Worksheet
' Letting our Variables equal something
Set PeopleWorkbook = ActiveWorkbook
Set People = PeopleWorkbook.Worksheets("People")
Set UnitedKingdom = PeopleWorkbook.Worksheets("United Kingdom")
Set UnitedStates = PeopleWorkbook.Worksheets("United States")
Set Canada = PeopleWorkbook.Worksheets("Canada")
' Defining ranges
Dim PeopleDataRange As Range
' C1 is cell 1 - C2, cell 2
Dim C1 As Range
Dim C2 As Range
' Equating C1 to the cell that is at the 2nd row first column
' Equating C2 to the cell that is at the last row position first column
Set C1 = People.Cells(2, 1)
Set C2 = People.Cells(People.UsedRange.Rows.Count, 1)
' Creating a range out of these two cells
Set PeopleDataRange = People.Range(C1, C2)
' Printing length of Range
Debug.Print PeopleDataRange.Count
' For each row
Dim ThisWorks As Boolean

For Each c In PeopleDataRange.Rows
        If People.Rows(c, 2).Value = "United States" Then
            ThisWorks = True
        Else
            ThisWorks = False
        End If
Next c
End Sub

I am currently just debugging this - that is why it doesn't actually do anything aside from changing the value of a boolean. I am getting an error at

If People.Rows(c, 2).Value = "United States" Then Error Code: 1004 - Application or Object Defined Error

The workflow should be as follows - Index through column searching for specific string match, once found - copy a selection of columns.

IE If a string match is found on Row 35 - Return Row 35 column A,b,c,d... to a new sheet.

Main Data

Sheet to have data inserted

*Edit - The Expected results should be as the loop iterates each time - the boolean value should turn true when the value matches the lookup

1:True 2:False 3:True 4:False 5:False 6:False *

Upvotes: 0

Views: 67

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

c is not a row number but a range containing the cell in column A. You need to refer to the row:

If People.cells(c.row, 2).Value = "United States" Then

Upvotes: 1

Related Questions