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

[raham]

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,

 

[raham]

What the mistake was you doing is that the header rows are empty. Just fill all five heaader cells of column A of both the sheets 1 & 2 (with anything) and run the macro...

Sub Find()
Dim RowSheet1 As Long, RowSheet2 As Long, X As Long, i As Long, Counter As Long
RowSheet1 = Application.CountA(Sheets(1).Columns(1))
For X = 6 To RowSheet1
    RowSheet2 = Application.CountA(Sheets(2).Columns(1))
    Counter = 5
    For i = 6 To RowSheet2
        If Sheets(2).Cells(i, 1).Value = Sheets(1).Cells(X, 1).Value Then
            If Sheets(2).Cells(i, 3).Value < Sheets(1).Cells(X, 2).Value Then
                Sheets(2).Cells(i, 3).Value = Sheets(1).Cells(X, 2).Value
                Sheets(2).Cells(i, 2).Value = Sheets(2).Cells(i, 2).Value + 1
                On Error Resume Next
                Sheets(2).Cells(i, 4).Value = Application.WorksheetFunction.VLookup(Sheets(2).Cells(i, 1).Value, Sheets(3).Range("A:B"), 2, False)
            End If
            Exit For
        Else
            Counter = i
        End If
    Next i
    
    If Counter = RowSheet2 Then
        Sheets(2).Cells(Counter + 1, 1).Value = Sheets(1).Cells(X, 1).Value
        Sheets(2).Cells(Counter + 1, 2).Value = 1
        Sheets(2).Cells(Counter + 1, 3).Value = Sheets(1).Cells(X, 2).Value
        On Error Resume Next
        Sheets(2).Cells(Counter + 1, 4).Value = Application.VLookup(Sheets(2).Cells(Counter + 1, 1).Value, Sheets(3).Range("A:B"), 2, False)
    End If
Next X
Sheets(2).Activate
End Sub

Dear Sachin2k6,

Wow, I managed to get it to work, thank you very much for your help,
But when I put this in my real application it does not work.

https://www.dropbox.com/sh/9kb4dpgml100ehi/LA4m2VFfxA

I am wondering if you can help me with solving this issue please,
Once again thank you for your help,

 

[Sachin2k6]

Just format all the cells containing mobile no. as 'number cells' instead of 'text cells' and the macro will get in to work instantly.

 

[raham]

Just format all the cells containing mobile no. as 'number cells' instead of 'text cells' and the macro will get in to work instantly.

Sachin2k6,

Thank you for your help but I cant get it to work,

I tried the following two works but I can not get it to work,

1) Manually

Right Click > Format Cells > Number > Ok

2) VBA

Sheets(1).Columns(1).NumberFormat = "0"
Sheets(2).Columns(1).NumberFormat = "0"
Sheets(3).Columns(1).NumberFormat = "0"

Am I doing it right?

 

[Sachin2k6]

Sachin2k6,

Thank you for your help but I cant get it to work,

I tried the following two works but I can not get it to work,

1) Manually

Right Click > Format Cells > Number > Ok

2) VBA

Sheets(1).Columns(1).NumberFormat = "0"
Sheets(2).Columns(1).NumberFormat = "0"
Sheets(3).Columns(1).NumberFormat = "0"

Am I doing it right?

No.

Do the following steps :

click the uppermost cell containg data

An error balloon will appear

Now press shift and and scroll down up to visible range(untill the balloon is visible)

Click this ballon and select the option 'Convert to number'

Now you can apply the desired formatting to the cell(s)

 

[raham]

No.

Do the following steps :

click the uppermost cell containg data

An error balloon will appear

Now press shift and and scroll down up to visible range(untill the balloon is visible)

Click this ballon and select the option 'Convert to number'

Now you can apply the desired formatting to the cell(s)

Sachin2k6,

I cant get it to work! I did the steps but nothing yet!

https://www.dropbox.com/sh/9kb4dpgml100ehi/LA4m2VFfxA

Thank you,

 

[Sachin2k6]

Sorry i forgot to tell you one more thing earlier.

In your sheet rows 5 and 6 are merged. Because of this macro is not getting in-to act.

Just unmerge these cell and macro will get in to work as i tested.

Remember to fill the empty cell due to unmerging.

Now i think i have finished from my side.

Hope your thread will be resolved.

Have a good time.

 

[raham]

Sorry i forgot to tell you one more thing earlier.

In your sheet rows 5 and 6 are merged. Because of this macro is not getting in-to act.

Just unmerge these cell and macro will get in to work as i tested.

Remember to fill the empty cell due to unmerging.

Now i think i have finished from my side.

Hope your thread will be resolved.

Have a good time.

Sachin2k6,

Done it, thank you for your support