VBA Excel matching criteria across sheets and columns

Question

I am trying to compare data across two sheets in the same workbook. First sheet has a list of individual addresses and the second has a list of address ranges where one column is the starting address range and the second column is the ending address range. for example

sheet1: 
123 main st
230     main st
456 main st


Sheet2: 
100 200 main st
400 500 main st

How do I find if an individual address falls within an address range? I have the below code that matches on the street name, but I need to add the criteria for the street number falling within that address range, otherwise it's not a match. In this example, sheet1 rows 1 and 3 is a match and sheet1 row 2 is not a match.

Sub matchcolumns()

Dim I, total, fRow As Integer
Dim found As Range

total = Sheets(1).Range("A" & Rows.Count).End(xlUp).row

For I = 2 To total

    answer1 = Worksheets(2).Range("A" & I).Value
    Set found = Sheets(1).Columns("H:H").Find(what:=answer1) 'finds a match

        If Not found Is Nothing Then
            Debug.Print "MATCH"
        Else
            Debug.Print "NO MATCH"
        End If
Next I

End Sub

Answer 1

@Mikku thanks I read the data as poorly formatted columns ... not as a single column. My mistake. Here is the updated code to work on single column data. I've made simplistic assumptions on data types (and left street numbers as strings given I've no idea on how they might really be structured) etc ... but works with the data examples used in question:

Option Explicit

Public Sub check()

    Dim vDataSheet As Worksheet
    Dim vDataRow As Long

    Dim vStreetNumber As String
    Dim vStreetName As String

    Dim vRefSheet As Worksheet
    Dim vRefRow As Long

    Dim vFromNumber As String
    Dim vToNumber As String

    Dim vFirstSpace As Long
    Dim vSecondspace As Long

    Dim vRefName As String

    Dim vFound As Boolean

    Set vDataSheet = Application.ActiveWorkbook.Sheets("Sheet1")
    Set vRefSheet = Application.ActiveWorkbook.Sheets("Sheet2")

    vDataRow = 1
    While vDataSheet.Cells(vDataRow, 1) <> ""

        vStreetNumber = Left(vDataSheet.Cells(vDataRow, 1), InStr(1, vDataSheet.Cells(vDataRow, 1), " ") - 1)
        vStreetName = Right(vDataSheet.Cells(vDataRow, 1), Len(vDataSheet.Cells(vDataRow, 1)) - InStr(1, vDataSheet.Cells(vDataRow, 1), " "))

        vFound = False
        vRefRow = 1
        While vRefSheet.Cells(vRefRow, 1) <> "" And Not vFound

            vFirstSpace = InStr(1, vRefSheet.Cells(vRefRow, 1), " ")
            vFromNumber = Left(vRefSheet.Cells(vRefRow, 1), vFirstSpace - 1)

            vSecondspace = InStr(vFirstSpace + 1, vRefSheet.Cells(vRefRow, 1), " ")
            vToNumber = Mid(vRefSheet.Cells(vRefRow, 1), vFirstSpace + 1, vSecondspace - vFirstSpace - 1)

            vRefName = Right(vRefSheet.Cells(vRefRow, 1), Len(vRefSheet.Cells(vRefRow, 1)) - vSecondspace)

            If vStreetNumber >= vFromNumber And vStreetNumber <= vToNumber And _
               vStreetName = vRefName Then
                vFound = True
            End If

            vRefRow = vRefRow + 1
        Wend

        If vFound Then
            vDataSheet.Cells(vDataRow, 2) = "MATCH"
        Else
            vDataSheet.Cells(vDataRow, 2) = "NO MATCH"
        End If

        vDataRow = vDataRow + 1
    Wend

End Sub

Reference data on Sheet2

Matching outcome on Sheet1

Answer 2

Loop through Sheet1 and check whether it exists in Sheet2. In this instance, MATCH or NO MATCH is written in the third column. Cheers.

Option Explicit

Public Sub check()

    Dim vDataSheet As Worksheet
    Dim vDataRow As Long

    Dim vRefSheet As Worksheet
    Dim vRefRow As Long

    Dim vFound As Boolean

    Set vDataSheet = Application.ActiveWorkbook.Sheets("Sheet1")
    Set vRefSheet = Application.ActiveWorkbook.Sheets("Sheet2")

    vDataRow = 1
    While vDataSheet.Cells(vDataRow, 1) <> ""

        vFound = False
        vRefRow = 1
        While vRefSheet.Cells(vRefRow, 1) <> "" And Not vFound

            If vDataSheet.Cells(vDataRow, 1) >= vRefSheet.Cells(vRefRow, 1) And _
               vDataSheet.Cells(vDataRow, 1) <= vRefSheet.Cells(vRefRow, 2) And _
               vDataSheet.Cells(vDataRow, 2) = vRefSheet.Cells(vRefRow, 3) Then
                vFound = True
            End If

            vRefRow = vRefRow + 1
        Wend

        If vFound Then
            vDataSheet.Cells(vDataRow, 3) = "MATCH"
        Else
            vDataSheet.Cells(vDataRow, 3) = "NO MATCH"
        End If

        vDataRow = vDataRow + 1
    Wend

End Sub

Sheet1 Before

Sheet2

Sheet1 After