+ Reply to Thread
Results 1 to 7 of 7

How to make case insensitive within a MACRO

  1. #1
    Registered User
    Join Date
    04-24-2012
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Wink How to make case insensitive within a MACRO

    Hi ALL!!

    I have currently put together some code to enable me to compare two different data sources main HR file (This data is over 6000 lines long with approximetly 15 columns worth of data to be compared)

    The Macro I have created so far is fit for the purpose in which I need it, however needs tweeking slightly in the way that it does not treat a upper and lower case as a difference.

    Example 17B Glen Avenue, and 17b Glen Avenue is showing as a differences within my report but I'd like this not to happen.

    I believe you can use a good few different options to make this work however what I have tried so far didnt work, therefore my question is, can anyone please help me to get the below code to be case insensitive???

    I believe there is UPPER and LOWER, and also LIKE that can be used but I'm clearly not placing this correctly within my coding.

    ALL HELP IS GREATLY APPRECIATED, in advance!!

    ______________________________________________________________________________________________________
    Sub Compare()
    '
    ' Macro1 Macro
    '
    ' compare two different worksheets in the active workbook
    CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
    End Sub

    Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
    Dim dupRow As Boolean
    Dim r As Long, c As Integer, m As Integer
    Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer, lr3 As Long
    Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
    Dim dupCount As Long

    Application.ScreenUpdating = False
    Application.StatusBar = "Creating the report..."
    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
    lr3 = 1
    For i = 1 To lr1
    dupRow = True
    Application.StatusBar = "Comparing cells " & Format(i / maxR, "0 %") & "..."
    For r = 1 To lr2
    For c = 1 To maxC
    ws1.Select
    cf1 = ""
    cf2 = ""
    On Error Resume Next
    cf1 = ws1.Cells(i, c).FormulaLocal
    cf2 = ws2.Cells(r, c).FormulaLocal
    On Error GoTo 0
    If cf1 <> cf2 Then
    dupRow = False
    Exit For
    Else
    dupRow = True
    End If
    Next c
    If dupRow Then
    dupCount = dupCount + 1
    ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, maxC)).Select
    Selection.Copy
    Worksheets("Sheet3").Select
    Worksheets("Sheet3").Range(Worksheets("Sheet3").Cells(lr3, 1), Worksheets("Sheet3").Cells(lr3, maxC)).Select
    Selection.PasteSpecial
    lr3 = lr3 + 1
    ws1.Select
    For t = 1 To maxC
    ws1.Cells(i, t).Interior.ColorIndex = 19
    ws1.Cells(i, t).Select
    Selection.Font.Bold = True
    Next t
    End If
    Next r
    Next i
    Application.StatusBar = "Formatting the report..."
    'Columns("A:IV").ColumnWidth = 10
    m = dupCount
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox m & " Rows contain same values!", vbInformation, _
    "Compare " & ws1.Name & " LIKE " & ws2.Name
    End Sub

  2. #2
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: How to make case insensitive within a MACRO

    You can use UCase within VBA:

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,486

    Re: How to make case insensitive within a MACRO

    Have a look in the help pages for the "option compare" statement.

  4. #4
    Registered User
    Join Date
    04-24-2012
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Cool Re: How to make case insensitive within a MACRO

    Hi All,

    Thanks for your suggestions.

    wallyeye - Can I please ask that you look at some other code I have and see if this would need something different then ucase as would I be correct in saying this ucase option is only available within a String?? I totally appreciated your help as I'm currently just trying to teach myself the ways around a VBA Macro.

    Sub LookForDiscrepancies()
    Dim varS1, varS2, varH1, varH2
    Dim rngS1 As Range, rngS2 As Range
    Dim c As Range, c1 As Range, c2 As Range
    Dim iRow As Integer, iCol As Integer, i As Integer, iTest As Integer

    Sheet1.Activate
    Set rngS1 = Intersect(Sheet1.UsedRange, Columns("A"))
    Sheet2.Activate
    Set rngS2 = Intersect(Sheet2.UsedRange, Columns("A"))
    Sheet3.Activate

    Let iRow = iRow + 2
    With rngS2
    'Search for Sheet1 AU IDs on Sheet2
    For Each c1 In rngS1
    On Error GoTo 0
    Set c = .Find(what:=c1.Value) 'Look for match
    If c Is Nothing Then 'Copy the AU ID to Sheet3
    Sheet3.Cells(iRow, 1) = c1
    Let iRow = iRow + 1
    Else 'Check if rows are identical
    Let varS1 = Intersect(Sheet1.UsedRange, c1.EntireRow)
    Let varS2 = Intersect(Sheet2.UsedRange, c.EntireRow)
    Let iCol = Intersect(Sheet1.UsedRange, c1.EntireRow).Count
    ReDim varH1(1 To iCol) As Integer
    For i = 1 To iCol
    If Not varS1(1, i) = varS2(1, i) Then
    Let iTest = iTest + 1
    Let varH1(i) = 1
    End If
    Next i

    If iTest Then 'Rows are not identical
    For i = 1 To iCol
    Sheet3.Cells(iRow, i) = varS1(1, i)
    If Not varH1(i) = 0 Then Cells(iRow, i) _
    .Interior.ColorIndex = 36
    Next i
    Let iTest = 0
    Let iRow = iRow + 1
    End If
    End If
    Next
    End With

    Let iRow = iRow + 2
    With rngS1
    'Search for Sheet2 AU IDs on Sheet1
    For Each c2 In rngS2
    On Error GoTo 0
    Set c = .Find(what:=c2.Value) 'Look for match
    If c Is Nothing Then 'Copy the AU ID to Sheet3
    Sheet3.Cells(iRow, 1) = c2
    Let iRow = iRow + 1
    Else 'Check if rows are identical
    Let varS1 = Intersect(Sheet2.UsedRange, c2.EntireRow)
    Let varS2 = Intersect(Sheet1.UsedRange, c.EntireRow)
    Let iCol = Intersect(Sheet2.UsedRange, c2.EntireRow).Count
    ReDim varH2(1 To iCol) As Integer
    For i = 1 To iCol
    If Not varS1(1, i) = varS2(1, i) Then
    Let iTest = iTest + 1
    Let varH2(i) = 1
    End If
    Next i
    If iTest Then 'Rows are not identical
    For i = 1 To iCol
    Sheet3.Cells(iRow, i) = varS1(1, i)
    If Not varH2(i) = 0 Then Cells(iRow, i) _
    .Interior.ColorIndex = 36
    Next i
    Let iTest = 0
    Let iRow = iRow + 1
    End If
    End If
    Next
    End With
    End Sub

    All help you are able to provide me is greatly appreciated.

    xx

  5. #5
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: How to make case insensitive within a MACRO

    You will probably want to use code tags when posting to this forum, just select the code and click the # on the toolbar. Makes code easier to read and stands it out a bit. MrShorty's suggestion of using option compare Text will work on the case changes as well, probably better than my ucase suggestion:

    Please Login or Register  to view this content.
    I put this in a code module, so the syntax checks out. I won't guarantee the logic, but it isn't modified much from yours and yours looked ok.

  6. #6
    Registered User
    Join Date
    04-24-2012
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    3

    Cool Re: How to make case insensitive within a MACRO

    Hi wallyeye,

    Firstly, thank you for this repost of code greatly appreciated.

    Only thing is, when I now run this it only show's me the AU ID on sheet3 opposed to bringing back the full line of data, and then showing me the exact cell were the difference occurs by highlighting this cell in yellow.

    On an plus side, usually It would bring back more than 2,000 lines on tab 3 as a difference, however this time it has only brought back just over 500 lines so I am assuming your code works for making this case insensative, however it just brought back the actual Unique identifier (so NI number) and not the whole line's data like I would require with the actual cell highlighted in yellow to show were the difference lies.

    Any suggestions are greatly welcome.
    x

  7. #7
    Forum Contributor wallyeye's Avatar
    Join Date
    05-06-2011
    Location
    Arizona
    MS-Off Ver
    Office 2010, 2007
    Posts
    308

    Re: How to make case insensitive within a MACRO

    A couple tweaks, being more specific about which sheet it is operating on, and being a bit more careful with the array dimensions:

    Please Login or Register  to view this content.

+ 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