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

R

raham

New Member
Joined
Jul 29, 2012
Messages
24
Dear Everyone,

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

Code: 
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,
 
Sort by date Sort by votes
Dear Guys,

I cant get any output when i run this code and i don't want to get a duplicated output

Code: 
Rowsheet1 = Application.CountA(Sheets(1).Columns(1))
Rowsheet2 = Application.CountA(Sheets(2).Columns(1))

For X = 1 To Rowsheet1

    For i = 1 To Rowsheet2
        If Sheets(2).Cells(i, 1).Value = Sheets(1).Cells(X, 1).Value Then
    Else
        EmpRow = Application.CountA(Sheets(2).Columns(1)) + 1
        Sheets(2).Cells(EmpRow, 1).Value = Sheets(1).Cells(X, 1).Value
        
        End If
    Next i
Next X
End
End
 
Last edited:
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
raham said:
Sachin2k6,
Thank you for your help,
What if there is no records in sheet 2?
Click to expand...

Try This

Code: 
RowSheet1=Application.CountA(Sheets(1).Columns(1))

For x = 2 to RowSheet1

RowSheet2=Application.CountA(Sheets(2).Columns(1))

For i = 2 To RowSheet2
If Sheets(2).Cells(i, 1).Value = Sheets(1).Cells(x, 1).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
Exit For
Else
Counter = i
End If
Next i
If Counter = RowSheet2 then
EmpRow = Application.CountA(Sheets(2).Columns(1))+1
Sheets(2).Cells(EmpRow, 1).Value= Sheets(1).Cells(x, 1).Value
Sheets(2).Cells(EmpRow, 2).Value = 1
Sheets(2).Cells(EmpRow, 3).Value= Sheets(1).Cells(x, 2).Value
End If
Next x
 
Upvote 0
If Your Data has headings in both sheets then try

Code: 
RowSheet1=Application.CountA(Sheets(1).Columns(1))


For x = 2 to RowSheet1


RowSheet2=Application.CountA(Sheets(2).Columns(1))
Counter=1


For i = 2 To RowSheet2
If Sheets(2).Cells(i, 1).Value = Sheets(1).Cells(x, 1).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
Exit For
Else
Counter = i
End If
Next i
If Counter = RowSheet2 then
EmpRow = Application.CountA(Sheets(2).Columns(1))+1
Sheets(2).Cells(EmpRow, 1).Value= Sheets(1).Cells(x, 1).Value
Sheets(2).Cells(EmpRow, 2).Value = 1
Sheets(2).Cells(EmpRow, 3).Value= Sheets(1).Cells(x, 2).Value
End If
Next x


Else if your data does not have headings then start both the loop counters from 1 instead of 2 and do't initialize the variable 'Counter' to 1 i.e. remove the line Counter=1.
 
Upvote 0
Sachin2k6,
Thank you for your help, I have another question which i confused a bit,
look at this code,

Code: 
......
......
......

LR1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'MsgBox LR2

For X = 7 To LR1
    For i = 7 To LR2
        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
                    Exit For
        End If
        Else
            Counter = i
        End If
    Next i
Next X

........
........
........

It works fine when I run it on its own but when i combine it with some other code it does not execute, I tested all part of my code as whole, the code above and below it works fine just this part which is not working.
I am wondering if you may know the answer to it,
 
Upvote 0
What do you want to do actually & what error are you getting?

Did My code resolve your problem or not?

May be some variable's value is changing in middle of the code when you combine it with some other lines of code.
 
Last edited:
Upvote 0
Sachin2k6 said:
What do you want to do actually & what error are you getting?

Did My code resolve your problem or not?

May be some variable's value is changing in middle of the code when you combine it with some other lines of code.
Click to expand...

Dear Sachin2k6,

As you see from my code its a version of your code with some modification according to my needs.
it does not giving me any error jut not exciting,

let me tell you in detail what i want to do, let me come back to my original post,
Sheet 1 (input data):

NoDate
103/08/2012
203/08/2012
303/08/2012
403/08/2012
303/08/2012
203/08/2012
103/08/2012
603/08/2012
1003/08/2012

<tbody>
</tbody>


Sheet 2:

NoTimesDate
1001/08/2012
7001/08/2012
2001/08/2012
8001/08/2012
3001/08/2012
9001/08/2012
4001/08/2012
5001/08/2012
6001/08/2012

<tbody>
</tbody>


Sheet 3

NoText
1QAZ
2WSX
3EDC
4RFV
3TGB
2YHN
1UJM
6IKO
10POK

<tbody>
</tbody>

Expected output:

NoTimeDateText
1203/08/2012...
7001/08/2012...
2203/08/2012...
8001/08/2012...
3203/08/2012...
9001/08/2012...
4103/08/2012...
5001/08/2012...
6103/08/2012...
10103/08/2012...

<tbody>
</tbody>

Here is what I am trying to achieve:


  • Every time find the same No in sheet 2 add 1 to time column (if the date is newer, then add otherwise don't),
  • Update the date in sheet 2 (if its newer)
  • If there is no No in sheet 2, add to list and update time and date,
  • Also find some extra information in sheet 3 which will be add to sheet 2
  • Perform all of these operation form scratch i.e. no data available in sheet 2 (in start)
  • Every time new set of input added to sheet 1 perform the same operation and jut update the previous data in sheet 2

Here is me whole code:

Code: 
Private Sub Find_Click()

Dim LR As Long, i As Long

LR1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

For i = LR1 To 2 Step -1
If WorksheetFunction.CountIf(Columns("A"), _
Range("A" & i).Value) = 0 Then

EmpRow = Application.CountA(Sheets(2).Columns(1)) + 1
Sheets(2).Cells(EmpRow, 1).Value = Sheets(1).Cells(i, 1).Value

ElseIf WorksheetFunction.CountIf(Columns("A"), _
Range("A" & i).Value) > 1 Then Rows(i).Delete

End If
Next i

[COLOR=#ff0000]************************
Here is what I planed/wanted to do
Copy sheet 1, column  1  entries to sheet 2 to column 1,
Then delete the duplicated entries 
*************************[/COLOR]

Rowsheet1 = Application.CountA(Sheets(1).Columns(1))
Rowsheet2 = Application.CountA(Sheets(2).Columns(1))


For X = 1 To Rowsheet1
    For i = 1 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
                    Exit For
        End If
        Else
            Counter = i
        End If
    Next i
Next X
'End
'End

[COLOR=#ff0000]************************
Then,
if the date is newer add to sheet 2 and set time to +1 
*************************[/COLOR]

Rowsheet3 = Application.CountA(Sheets(3).Columns(1))

For X = 1 To Rowsheet2
    For i = 1 To Rowsheet3
        If Sheets(3).Cells(i, 1).Value = Sheets(2).Cells(X, 1).Value Then
                    Sheets(2).Cells(X, 4).Value = Sheets(3).Cells(i, 2).Value
                    Exit For
        Else
            Counter = i
        End If
    Next i
Next X
End
End

[COLOR=#ff0000]************************
Then,
Find same entery in sheet 3 and copy some extra information to sheet 2
*************************
[/COLOR]
End Sub

Here is the whole process that I am after to do,
Thank you for your support,
 
Last edited:
Upvote 0
One more thing,

The data in each sheet is available from 7th row since rows 1 to 6 are freeze panes,
 
Upvote 0
The below macro do exactly what you want :

Code: 
Sub FindClick()


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 = 2 To RowSheet1
    RowSheet2 = Application.CountA(Sheets(2).Columns(1))
    Counter = 1
    For i = 2 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
 
Upvote 0
Sachin2k6 said:
The below macro do exactly what you want :

Code: 
Sub FindClick()


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 = 2 To RowSheet1
    RowSheet2 = Application.CountA(Sheets(2).Columns(1))
    Counter = 1
    For i = 2 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
Click to expand...


Dear Sachin2k6

Thank you for your help, Unfortunately I could not get it to work, here is a copy of my file,

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

Thank you,
 
Upvote 0
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...


Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
VBA compare values on different sheets.
Replies
5
Views
191
rlv01
R
J
  • Solved
VBA to compare rows in two different sheets, if match highlight cell
2
Replies
10
Views
829
kevin9999
K
K
  • Question
vba code for unlocking cell after paste
Replies
1
Views
88
MikeVol
MikeVol
K
  • Question
How to shorten vba code without any problems
Replies
1
Views
61
kamranyd
K
K
  • Question
how to copy above cell values down
Replies
5
Views
147
kamranyd
K

Forum statistics

Threads
1,216,212
Messages
6,129,540
Members
449,515
Latest member
lukaderanged

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top