VBA for Cell to Cell comparision

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
81
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.
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
98
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This copies the top row of the selected area of the Prod sheet as the Score sheet title while making the comparison.
Is it a suitable solution for copying titles?

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
On Error GoTo ErrHand
Application.Calculation = xlManual: Application.ScreenUpdating = False

    Set ProdWs = ThisWorkbook.Sheets("Prod"): Set QCWs = ThisWorkbook.Sheets("QC"): Set ScoreWs = ThisWorkbook.Sheets("Score")
    Set ProdRNG = Selection
    Set ScoreRNG = ScoreWs.Range(ProdRNG.Address)
            ProdRNG.Rows(1).Copy ScoreRNG.Rows(1)
    Set ProdRNG = ProdRNG.Offset(1, 0).Resize(ProdRNG.Rows.Count - 1, ProdRNG.Columns.Count)
    Set ScoreRNG = ScoreWs.Range(ProdRNG.Address)
    Set QCRNG = QCWs.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
ErrHand:
Application.Calculation = xlAutomatic: Application.ScreenUpdating = True
End Sub
 
Solution

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
98
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you! Nice to hear that I was able to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,832
Messages
5,661,200
Members
418,620
Latest member
JUrq

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
Top