Posts by korani

    Sub tgr()

    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim rSourceHCol As Range
    Dim rSourceHCell As Range
    Dim rDestHCol As Range
    Dim rFound As Range
    Dim sFirst As String
    Dim sNotFound As String

    Set wb = ActiveWorkbook
    Set wsSource = wb.Sheets("Sheet1")
    Set wsDest = wb.Sheets("Sheet2")
    Set rSourceHCol = wsSource.Range("H2", wsSource.Cells(wsSource.Rows.Count, "H").End(xlUp))
    Set rDestHCol = wsDest.Range("H2", wsDest.Cells(wsDest.Rows.Count, "H").End(xlUp))

    If rSourceHCol.Row < 2 Then
    MsgBox "No values present in column H of source sheet " & wsSource.Name
    Exit Sub
    ElseIf rDestHCol.Row < 2 Then
    MsgBox "No values present in column H of destination sheet " & wsDest.Name
    Exit Sub
    End If

    For Each rSourceHCell In rSourceHCol.Cells
    Set rFound = rDestHCol.Find(rSourceHCell.Value, rDestHCol.Cells(rDestHCol.Cells.Count), xlValues, xlWhole)
    If rFound Is Nothing Then
    sNotFound = sNotFound & Chr(10) & rSourceHCell.Value
    sFirst = rFound.Address
    rFound.Offset(, -6).Value = rSourceHCell.Offset(, -6).Value
    Set rFound = rDestHCol.FindNext(rFound)
    Loop While rFound.Address <> sFirst
    End If
    Next rSourceHCell

    If Len(sNotFound) = 0 Then
    MsgBox "All values from source data accounted for and updated in destination"
    MsgBox "The following values in the source data were not found in destination:" & sNotFound
    End If

    End Sub

    Basically i have this script which compare 2 sheets, which compares a value in a column to the new sheet, if it finds the value, it will copy the information from Old sheet "B" to new sheet "B" column.

    The script is working flawlessly (Thanks to the author)

    I have trying to configure it to search and compare not only 1 column, but 2, if column X AND Y are equal to X AND Y in the new sheet it will do the same task.

    The reason for this is that sometimes i have the value it searches for in few different rows, so when it compares it will find it at few places. While this script works perfect only when there are unique "Find" values.

    Can you help me to edit so it fits "Find" and compare Column "P" & Column "V" if those are the same in new sheet, it will copy the Values in Column "B" old sheet to "B" new sheet.

    So i need to tweak this to search both column P, AND Column V, if both are correct then it will do the tast, if something is wrong it will tell me what Tag is wrong.
    The author told me this should be easy change, but for me it is not.


    @JonasKorani That should be a relatively easy change. In the Do Loop, you'd need to add a check for the value in the second column(s). If you get stuck on that, please start a new question (feel free to provide a link to this one) with what you've tried. – tigeravatar 2 days ago

    Link to original:

    Link to someone tried to help me, but didnt work as correctly:

    Copy row if text in column, replace row with same row in another sheet

    Hello guys. I have been working on a problem for a while, and can not find out how to solve it.
    I have 2 Sheets, "Sheet1" and "Sheet2"
    Data contains alot of text, around 750k of cells to be specific and it is linked to a listbox where i can edit data, add comments etc.
    Data in sheet1 is ranged by ID Column, it can search for the ID in the listbox and will write comments to the correct Row based on listbox so this part works flawlessly.

    My problem is that sometimes we need to update the Data sheet to a new one without ID column, and since new data might change from the old one, old ID will not match correctly with the sheet2,
    So we want to copy all the "Comments" from the old Data, and find the tag in the "Data New" Sheet2 and paste them to the correct tag, without using ID, maybe more like search and paste function.

    I have tried to find a method that searched tag1 in the Data sheet1, searches for it in "Data new sheet2" and paste the whole row in there, but didnt work either, i have also tried to copy text from comment section of sheet1, seach tagname on the same row, find it in sheet2 and paste only the comment with offset function, but that was not easy either.

    NB: Just to be clear, Sheet1 and sheet2 contains almost the same Data.
    Sheet2 dont have the ID and Comments, but might have few more rows with extra data or changed data from sheet1

    I want it to search for column that contains text (B:B) copy that text and search for the Tag1 in the "Data New" sheet find that tag there and paste it there.

    I think maybe easiest way will be to find all the rows with comment, search tag in that row in new list and replace the whole row. OR

    Copy all rows that has text in B:B (or Not empty will also work maybe) and put it somehow to new sheet replacing the same data there
    Sorry if this is complicated to explain...