Compare two columns and copy paste using vba

Question

I have two columns in a sheet "test". Let's assume col C and D.

Each rows in C and D might have either "COMPATIBLE" or "NOT DETERMINED" or Blank cell.

I want to compare col C and D,and if C has "COMPATIBLE" and D has "NOT DETERMINED", then "COMPATIBLE" should be paste into D and vice versa.

I have below code, But not sure how to complete it:

Sub compare_cols()

'Get the last row
Dim Report As Worksheet
Dim i As Integer, j As Integer
Dim lastRow As Integer

Set Report = Excel.Worksheets("test") 'You could also use Excel.ActiveSheet _
                                        if you always want this to run on the current sheet.

lastRow = Report.UsedRange.Rows.Count

Application.ScreenUpdating = False

For i = 2 To lastRow
    For j = 2 To lastRow
        If Report.Cells(i, 1).Value = "COMPATIBLE" Then 
            If InStr(1, Report.Cells(j, 2).Value, Report.Cells(i, 1).Value, vbTextCompare) > 0

UPDATING THE WORK IN PROGRESS CODE:

Option Explicit

Sub compare_cols()
With Worksheets("Latency") '<-.-| reference your worksheet
    With .Range("F1:G" & .UsedRange.Rows(.UsedRange.Rows.count).Row) '<--| reference its columns C:D range from row 1 down to worksheet last used row
        Correct .Cells, "COMPATIBLE", "Not Determind", 2
        Correct .Cells, "Determind", "COMPATIBLE", 1
    End With
    .AutoFilterMode = False
End With
End Sub

Sub Correct(rng As Range, val1 As String, val2 As String, colToChangeIndex As Long)
With rng '<--| reference passed range
    .AutoFilter Field:=1, Criteria1:=val1 '<--| filter referenced range on its 1st column with 'val1'
    .AutoFilter Field:=2, Criteria1:=val2 '<--| filter referenced range on its 2nd column with 'val2'
    If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then '<--| if any filterd cells other than header
        .Resize(.Rows.count - 1, 1).Offset(1, colToChangeIndex - 1).SpecialCells(xlCellTypeVisible).Value = "COMPATIBLE" '<--| write "COMPATIBLE" in column "D"
    End If
End With
End Sub

Is that all the code? ...you need to close your If and For statements, first of all. What have you tried? And finish the second If statement... — BruceWayne
– BruceWayne, Commented Jan 13, 2017 at 4:17
Yes I'm stuck with this. I'm not sure how to close it and complete the code — walter while
– walter while, Commented Jan 13, 2017 at 4:26

nightcrawler23 · Accepted Answer · 2017-01-13 04:30:18Z

1

Try this code

Sub CvalueAndDvalue()
    Dim cValue As Range, dValue As Range

    Dim Report As Worksheet
    Set Report = Excel.Worksheets("test")

    For i = 2 To Report.Range("C" & Rows.Count).End(xlUp).Row
        Set cValue = Report.Range("C" & i)
        Set dValue = Report.Range("D" & i)

        If (Trim(cValue) = "COMPATIBLE" And Trim(dValue) = "NOT DETERMINED") Then
            dValue = cValue
            ElseIf (Trim(dValue) = "COMPATIBLE" And Trim(cValue) = "NOT DETERMINED") Then
            cValue = dValue
        End If
    Next i
End Sub

answered Jan 13, 2017 at 4:30

nightcrawler23

2,0761 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nightcrawler23 Over a year ago

added qualified ranges

walter while Over a year ago

In what format should i specify the range? Lets say I need to check in both the cols from rows 1 to 100

nightcrawler23 Over a year ago

the code will check both columns C and D, until the last row in column C. I don't understand what you mean by "In what format should i specify the range"

user3598756 · Accepted Answer · 2017-01-13 10:36:28Z

0

you could use AutoFilter():

Option Explicit

Sub compare_cols()
    With Worksheets("test") '<-.-| reference your worksheet
        With .Range("C1:D" & .UsedRange.Rows(.UsedRange.Rows.Count).Row) '<--| reference its columns C:D range from row 1 down to worksheet last used row
            Correct .Cells, "COMPATIBLE", "NOT DETERMINED", 2
            Correct .Cells, "NOT DETERMINED", "COMPATIBLE", 1
        End With
        .AutoFilterMode = False
    End With
End Sub

Sub Correct(rng As Range, val1 As String, val2 As String, colToChangeIndex As Long)
    With rng '<--| reference passed range
        .AutoFilter Field:=1, Criteria1:=val1 '<--| filter referenced range on its 1st column with 'val1'
        .AutoFilter Field:=2, Criteria1:=val2 '<--| filter referenced range on its 2nd column with 'val2'
        If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then '<--| if any filterd cells other than header
            .Resize(.Rows.Count - 1, 1).Offset(1, colToChangeIndex - 1).SpecialCells(xlCellTypeVisible).Value = "COMPATIBLE" '<--| write "COMPATIBLE" in column "D"
        End If
    End With
End Sub

edited Jan 13, 2017 at 10:36

answered Jan 13, 2017 at 7:19

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

15 Comments

walter while Over a year ago

I don't want to write "Not Determined" I just want to write "COMPATIBLE. and the code is not working..it runs, but i dont see any results

user3598756 Over a year ago

1) in your question you wrote ""COMPATIBLE" should be paste into D and vice versa.". now you say something different: please add examples of a "before" and "after" scenarios to clarify 2) code acts on worksheets and ranges as per comments: check if they fit your actual worksheet name and ranges: adding the beforesaid "scenarios" would help

walter while Over a year ago

Sorry for the confusion. What I meant was if C has "COMPATIBLE" and D has "NOT DETERMINED", then "COMPATIBLE" should be paste into D and if C has "NOT DETERMINED" and D has "COMPATIBLE", then "COMPATIBLE" should be paste into C. Example here - link

walter while Over a year ago

Just a doubt in With Worksheets("test") - I have a workbook and I need to run this code in a sheet named "test"...so that's what this code does right?

user3598756 Over a year ago

if you have only one workbook open then it suffices. if you have more than one workbook than add the proper workbook reference like With Workbooks("MyProperWorkbookName").Worksheets("test")

|

Collectives™ on Stack Overflow

Compare two columns and copy paste using vba

2 Answers 2

3 Comments

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related