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,
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Try this

Code: 
Sub foo()
For i = 1 To 159
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1) Then
'Do nothing
Else
MsgBox "Cell A" & i & " of Sheet1 does not equal Cell A" & i & " in Sheet2"
End If
Next i
End
End Sub
 
Upvote 0
jim may said:
Try this

Code: 
Sub foo()
For i = 1 To 159
If Sheets("sheet1").Cells(i, 1).Value = Sheets("sheet2").Cells(i, 1) Then
'Do nothing
Else
MsgBox "Cell A" & i & " of Sheet1 does not equal Cell A" & i & " in Sheet2"
End If
Next i
End
End Sub
Click to expand...

Dear Jim,

what if I want to find the value of a cell in sheet 1 in sheet 2,

lets say find the value of Sheets("sheet1").Cells(1, 1) in sheet 2, column 1, it can be in 1st row or 100th row,
 
Last edited:
Upvote 0
Paste into your sheets Sheet Code Window, not a Standard module

Then with Sheet1 Active -- Double click on any cell in the range A1:A159...

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("A1:A159"), Target.Value) > 0 Then
MsgBox "There is a match of this Value in Sheet2"
Else
MsgBox "There is no match of this value in Sheet2"
End If
End Sub
 
Upvote 0
jim may said:
Paste into your sheets Sheet Code Window, not a Standard module

Then with Sheet1 Active -- Double click on any cell in the range A1:A159...

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("A1:A159"), Target.Value) > 0 Then
MsgBox "There is a match of this Value in Sheet2"
Else
MsgBox "There is no match of this value in Sheet2"
End If
End Sub
Click to expand...

Dear Jim,

Thank you for your help but I need to find the position of it and add something to next column,

Here is an example of my work,

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>


Expected output:

NoTimeDate
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,
  • 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,

I hope you can understand now,
Thank you for your support,
 
Last edited:
Upvote 0
Assuming that sheet 2 has unique records , Try this

Code: 
For x = 1 to 10
    For i = 1 To 10
        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 = 10 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 = Sheets(2).Cells(i, 2).Value+1
        Sheets(2).Cells(EmpRow, 3).Value= Sheets(1).Cells(x, 2).Value
    End If
Next x
 
Upvote 0
Sachin2k6 said:
Assuming that sheet 2 has unique records , Try this

Code: 
For x = 1 to 10
    For i = 1 To 10
        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 = 10 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 = Sheets(2).Cells(i, 2).Value+1
        Sheets(2).Cells(EmpRow, 3).Value= Sheets(1).Cells(x, 2).Value
    End If
Next x
Click to expand...

Sachin2k6,
Thank you for your help,
What if there is no records in sheet 2?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
VBA compare values on different sheets.
Replies
5
Views
59
rlv01
R
J
  • Solved
VBA to compare rows in two different sheets, if match highlight cell
2
Replies
10
Views
662
kevin9999
K
T
  • Question
Comparing Concatenate Cells
Replies
4
Views
98
Fluff
Fluff
S
  • Solved
Search for file names that are not in a specific folder provided that their name is in a column
Replies
2
Views
214
sofas
S
A
  • Question
Conditional formating (with range) but instead of the color/formating of the certain cell to get values inside the matching cells in the range?
Replies
1
Views
198
aircarrier
A

Forum statistics

Threads
1,215,267
Messages
6,123,963
Members
449,137
Latest member
yeti1016

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