How to compare if a value inside a cell is the same

[Samsungg]

Dear Everyone,

I am wondering if you can write me a VBA code for comparing the value inside a cell.

For i = 1 To 159 
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1) then  
..............
Else
Sheets("sheet1").Cells(i, 1).Value = 0
End If
Next i
End

Thank you for your support,

[rvasquez]

Like this?

For i = 1 To 159 
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1).value then  
..............
Else
Sheets("sheet1").Cells(i, 1).Value = 0
End If
Next i
End

[Samsungg]

Dear rvasquez

thank you for your help, what if i want to find the value in Sheets("sheet1").Cells(i, 1) in Sheets("sheet2"). column "A"
how can i modifies the code?
Simply I want to find the value of Sheets("sheet1").Cells(i, 1) in sheet 2, column 1, it can be in 1st row or 100th row,

[rvasquez]

Okay, and do what with the found cell?

[Samsungg]

Add 1 to column 2, if find again add another 1 i.e. become 2

[rvasquez]

I'm sorry from what you provided you just want unique values added to sheet2? If this is not the case could you please revise your attachement and format sheet two to mimic what you would your results to look like?

Thanks!

[Samsungg]

rvasquez,

No, that right, I have edited the file for what i expected to get.

[rvasquez]

Dim c As Range, LR As String, LR1 As String

LR = Sheets(2).Range("A6555").End(xlUp).Row  'set LR equal to the last row in the second tab that contains a value

LR1 = Sheets(1).Range("A6555").End(xlUp).Row   'set LR1 equal to the last row in the first tab that contains a value

With Sheets(2)   ' with tab two 
    For Each c In .Range("A1:A" & LR).Cells   'loop through cells in column A, row 1 through Lr (defined above)

        With Sheets(1)   'with the first tab

            'set the value in column b of the current cell in the c loop, equal to the number of
            'times the value in column A appears in the first tab
            c.Offset(0, 1).Value = WorksheetFunction.CountIf(.Range("A1:A" & LR1), c.Value)
        End With
    Next c   ' move to next cell in the loop
End With

You could also achieve the same result by placing the formula in B1 and copying it down to the last row

=COUNTIF(Sheet1!A:A,A1)

To insert the macro above into your workbook:

1.Press Alt+F8 on your keyboard
2.Clear the macro name box and type LocateCells in the blank box provided
3.Select the Create option
4.In between the Sub LocateCells() and End Sub copy and paste the above code I have provided. Anything that appears in green is a comment.
5.Exit out of the Visual Basic Window
6.Press Alt+F8 again and this time select the LocateCells macro
7.Select Run

[Samsungg]

rvasquez,
Thank you for your help,
your code is valid only if same date available in sheet 2 compared to sheet 1. but, what if there is nothing in sheet 2? I found that the code is not working,
I do understand that i can do it through formula be i prefer VBA,

[rvasquez]

If it's not found then it should give you a 0.