+ Reply to Thread
Results 1 to 2 of 2

Compare formula not working

  1. #1
    Registered User
    Join Date
    02-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    1

    Compare formula not working

    I am trying to compare vertical range "A" in sheet1 to vertical range "K" in sheet2 if they match to copy both rows and place in sheet3.
    I have tried the following but it did not work. Any suggestions would be greatly appreciated.

    Sub Searching()
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    ' create short references to sheets
    ' inside the Sheets() use either the tab number or name
    Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets(2): Set sh3 = Sheets(3)

    ' Create iterators
    Dim i As Long, j As Long
    ' Create last rows values for the columns you will be comparing
    Dim lr1 As Long, lr2 As Long
    ' create a reference variable to the next available row in sheet3
    Dim nxtRow As Long
    ' Create ranges to easily reference data
    Dim rng1 As Range, rng2 As Range

    ' Assign values to variables
    lr1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = sh2.Range("K" & Rows.Count).End(xlUp).Row

    ' Clear sheet3
    sh3.Cells.ClearContents

    ' Loop through column A on sheet1
    For i = 1 To lr1
    Set rng1 = sh1.Range("A" & i)
    ' Loop through column A on sheet1
    For j = 1 To lr2
    Set rng2 = sh2.Range("K" & j)
    ' compare the words in column a on sheet1 with the words in column on sheet2
    If StrComp(CStr(rng1.Value), CStr(rng2.Value), vbTextCompare) = 0 Then
    ' now, the words match you want to compare the values
    If rng1.Offset(0, 1).Value = rng2.Offset(0, 1).Value Then
    ' the exact match is found
    ' manipulate data
    '
    ' find next empty row
    nxtRow = sh3.Range("A" & Rows.Count).End(xlUp).Row + 1
    ' copy the word in column A on sheet2 to the next available row in sheet3
    sh3.Range("A" & nxtRow).Value = rng2.Value
    ' copy the value ( offset(0,1) Column B ) to the next available row in sheet3
    sh3.Range("B" & nxtRow).Value = rng2.Offset(0, 1).Value
    ' display the matching index/row from sheet1 and sheet2
    sh3.Range("C" & nxtRow).NumberFormat = "@"
    sh3.Range("C" & nxtRow).Value = rng1.Row & "/" & rng2.Row
    End If
    End If
    Set rng2 = Nothing
    Next j
    Set rng1 = Nothing
    Next i
    sh3.Rows("1:1").Delete
    End Sub

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610

    Re: Compare formula not working

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


    To attach a Workbook
    (please do not post pictures of worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Correct/Working (Index,Match) formula not working between cells
    By barnerd in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-11-2014, 01:20 PM
  2. Compare two reports-Working but need help to automate the testing process
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-02-2013, 11:03 PM
  3. Replies: 2
    Last Post: 08-17-2012, 08:16 AM
  4. [SOLVED] How to compare todays date to an array of dates....not working.....
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2012, 09:27 PM
  5. Replies: 6
    Last Post: 05-07-2012, 10:49 AM

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