+ Reply to Thread
Results 1 to 2 of 2

Validating address with matching numbers

  1. #1
    Registered User
    Join Date
    09-08-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    20

    Validating address with matching numbers

    Hi all, I am seeking some help with regards to a project I am doing. Currently, I am suppose to validate the address information you see within the attachment within the two different sheets according to their identification number. However, when I started working on it, I didn't realize that sheet 1 contained more identification number than sheet 2. Therefore when I first applied my macro, the result came out all wrong as nothing matched.

    As a result, I need help with a macro that can sort through the two sheets that only target the same identification number and displaying both the address information.

    I thank you in advance for those who spent their time helping this beginner in Excel.

    Here is the code that I used to test a sample selection

    Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
    Dim r As Long, c As Integer
    Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
    Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
    Dim rptWB As Workbook, DiffCount As Long
    Application.ScreenUpdating = False
    Application.StatusBar = "Creating the report..."
    Set rptWB = Workbooks.Add
    Application.DisplayAlerts = False
    While Worksheets.Count > 1
    Worksheets(2).Delete
    Wend
    Application.DisplayAlerts = True
    With ws1.UsedRange
    lr1 = .Rows.Count
    lc1 = .Columns.Count
    End With
    With ws2.UsedRange
    lr2 = .Rows.Count
    lc2 = .Columns.Count
    End With
    maxR = lr1
    maxC = lc1
    If maxR < lr2 Then maxR = lr2
    If maxC < lc2 Then maxC = lc2
    DiffCount = 0
    For c = 1 To maxC
    Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..."
    For r = 1 To maxR
    cf1 = ""
    cf2 = ""
    On Error Resume Next
    cf1 = ws1.Cells(r, c).FormulaLocal
    cf2 = ws2.Cells(r, c).FormulaLocal
    On Error GoTo 0
    If cf1 <> cf2 Then
    DiffCount = DiffCount + 1
    Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
    End If
    Next r
    Next c
    Application.StatusBar = "Formatting the report..."
    With Range(Cells(1, 1), Cells(maxR, maxC))
    .Interior.ColorIndex = 19
    With .Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    End With
    With .Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    End With
    With .Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    End With
    With .Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    End With
    On Error Resume Next
    With .Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    End With
    With .Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    End With
    On Error GoTo 0
    End With
    Columns("A:IV").ColumnWidth = 20
    rptWB.Saved = True
    If DiffCount = 0 Then
    rptWB.Close False
    End If
    Set rptWB = Nothing
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox DiffCount & " cells contain different formulas!", vbInformation, _
    "Compare " & ws1.Name & " with " & ws2.Name
    End Sub

    Attached Files Attached Files

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Validating address with matching numbers

    hi Armored Wing

    This is the basic code you need somethig like,,, values on sheet 3

    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1