CODEWITHSUNDEEP
excelcomparisonvba
venkat
venkat

Reputation: 61

Compare values in Excel VBA

I am trying to compare cell A1 with B1 and if it is true populate cell F1 with the A1 value. But irrespective of my input values the if condition becomes true.

Sub Macro3()
    Dim i As Integer
    i = 1
    For i = 1 To 10
        If (Range("A" & i).Select = Range("B" & i).Select) Then
            Range("A" & i).Select
            Selection.Copy
            Range("F" & i).Select
            ActiveSheet.Paste
        End If
    Next i      
End Sub

Upvotes: 1

Views: 29989

Answers (3)

brettdj
brettdj

Reputation: 55682

You can use a variant array to address your performance issue that you raise above. This code will run the same as Nicks except it will skip blanks cell, ie it will

  1. update the F value if A and B are the same
  2. skip updates if the A cell is blank
  3. leave the existing F values in place if A<>B

It wasn't clear to me how you are comparing rows accross two sheets, can you expand on this?

    Sub MyArray()
    Dim X As Variant
    Dim Y As Variant
    Dim lngrow As Long
    X = Range([a1], Cells(Rows.Count, "B").End(xlUp))
    Y = Range([f1], [f1].Offset(UBound(X, 1) - 1, 0))
    For lngrow = 1 To UBound(X, 1)
       If Len(X(lngrow, 1)) > 0 Then
       If X(lngrow, 1) = X(lngrow, 2) Then Y(lngrow, 1) = X(lngrow, 1)
       End If
    Next
    Range([f1], [f1].Offset(UBound(X, 1) - 1, 0)) = Y
    End Sub

Upvotes: 0

Gaijinhunter
Gaijinhunter

Reputation: 14685

Consider this a compliment to Nick's answer (accept his if you find it to work, which you should). I wanted to help explain some of the things that are wrong in your code.

Before FIX:

Sub Macro3()
    Dim i As Integer
    i = 1
    For i = 1 To 10
        If (Range("A" & i).Select = Range("B" & i).Select) Then
            Range("A" & i).Select
            Selection.Copy
            Range("F" & i).Select
            ActiveSheet.Paste
        End If
    Next i
End Sub

AFTER FIX

Sub Macro4()
    Dim i As Long
    For i = 1 To 10
        If Range("A" & i).Value = Range("B" & i).Value Then
            Range("F" & i).Value = Range("A" & i).Value
        End If
    Next
End Sub

POINTS:

  1. Use Long instead of Integer (small optimization since VBA will convert the int to a long anyway)
  2. No need to declare i = 1 twice in a row
  3. You should be comparing values, not simply selecting cells. There is rarely, if ever, a need to use the .Select keyword. You can access all object's properties directly.
  4. Copy and paste is a heavy operation. Since you are in VBA, may as well just assign the value that is in A to the cell in column B. It's faster, and more effecient.

I hope this helps. BTW, you can simple enter:

=IF(A1=B1,A1,"")

in F1 and drag the formula down to get a similar result.

Upvotes: 0

CtrlDot
CtrlDot

Reputation: 3122

Instead of selecting, copying, and pasting, you can compare the Value property of the cells, then set the F column Value accordingly:

Dim i As Integer
For i = 1 To 10
    If Range("A" & i).Value = Range("B" & i).Value Then
        Range("F" & i).Value = Range("A" & i).Value
    End If
Next

Upvotes: 3

Related Questions