VBA for Cell to Cell comparision

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
90
Office Version
  1. 365
Platform
  1. Windows
Hi Team,

I need assistance with a Macro that can help me achieve a cell to cell comparison between two different worksheets within the same workbook and the corresponding score in the 3rd worksheet.

I have a workbook with 3 sheets.
1st sheet named - Prod
2nd sheet named - QC
3rd sheet named - Score

The data in sheet 'Prod' and sheet 'QC' will have the same column headers. Even the data size in both the sheet will be same. However the values may differ. I basically need a cell to cell comparison here.
Eg:
If Cell A1 in sheet Prod and Cell A1 in sheet QC matches, i need Cell A1 in Score sheet to give a value as 0. If it doesnt match then the value will be 1. Also please ensure that the values in the sheet 'Score' are values and should not be a formulae in the cell.


1615918497123.png


1615918534800.png


1615918607694.png



Please let me know if more information is needed.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
@Tupe77 I tried with 285 entries and 5000 entries... everytime it stopped at 282 with an overflow message.
 
Upvote 0
I tired again. This time i ensured all the formats stay as a table and I dont convert them to range.

and I changed to thi -

Dim Cell As Range, i As Long

and it worked for 285 entries. will try for 5000

Please see if it is possible for you to do the same with range rather than table.
 
Upvote 0
Hey @Fluff when i hit debug for subscript out of range, i the following code gets highlighted.

p = Sheets("Prod").ListObjects(1).DataBodyRange.Address(, , , 1)
 
Upvote 0
Do you have a sheet called "Prod" & is there a table on that sheet?
 
Upvote 0
How about this?
You must select wanted range from Prod Sheet before running this.

VBA Code:
Sub TS_TabToTabComp()
Dim ProdRNG As Range, QCRNG As Range, ScoreRNG As Range
Dim ProdWs As Worksheet, QCWs As Worksheet, ScoreWs As Worksheet
Dim Cell As Range, i As Long

Set ProdWs = ThisWorkbook.Sheets("Prod"): Set QCWs = ThisWorkbook.Sheets("QC"): Set ScoreWs = ThisWorkbook.Sheets("Score")

Set ProdRNG = Selection
Set QCRNG = QCWs.Range(ProdRNG.Address)
Set ScoreRNG = ScoreWs.Range(ProdRNG.Address)

i = 1
For Each Cell In ProdRNG
    If Cell.Value = QCRNG.Cells(i).Value Then
        ScoreRNG.Cells(i).Value = 0
    Else
        ScoreRNG.Cells(i).Value = 1
    End If
    i = i + 1
Next

End Sub

Or set wanted range to this line:
VBA Code:
Set ProdRNG = Selection
 
Upvote 0
I sort of understood what you mean @Tupe77 ... I do not have a fixed range though... it will vary based upon the volume of work.
 
Upvote 0
Can we create something on these lines ?

May be to simplify it further lets do it this way -

1. Let the 'Prod' , 'QC' and 'Score' sheet be blank.
2. I will later paste the data in both the sheets.
3. The macro should copy the header row 1 from 'Prod' sheet and paste it to 'Score' sheet.
4. Then it will compare cell to cell from row 2 onwards until the end range (the data may not be continuous - as above)

so when we run the macro with no data, it should give 1 as a value in every cell.

Hope that helps.
 
Upvote 0
@Tupe77

I like your idea of selecting the wanted Range in Prod sheet and then running the macro...makes life simple.. Kudos.

Can we add another line of macro which just copies the header of that selection and pastes to the header of sheet 'Score'... that could be after the cell to cell comparision.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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