Formula on 3rd sheet that maps to 1st two sheets corresponding cells

j0coder

New Member
Joined
Nov 2, 2017
Messages
6
A simple explanation of what I am trying to accomplish is this:

We have a .csv file that has a bit of post processing done on it. We take these 2 files and put them in 2 separate sheets within the same workbook in excel (done through VBScript). I then create a 3rd sheet and my goal is to write the Formula (=IF('Sheet1'!A1,A2,B1,B2,ect='Sheet1 (2)'!A1,A2,B1,B2,ect, 'TRUE', 'FALSE') into each cell of the 3rd sheet based off of the .UsedRange of the other 2 sheets.

I am able to access the .UsedRange of the 1st sheet and can manipulate the cell doing something like:

Code:
compareSheet.Range[COLOR=#000000]([/COLOR]tgtWorksheet.UsedRange.Address[COLOR=#000000])[/COLOR].Interior.ColorIndex [COLOR=#000000]=[/COLOR] [COLOR=#800000]4[/COLOR]

I have tried something similar to:

Code:
For Each cell In tgtWorksheet.UsedRange
    compareSheet.Range(cell).Interior.ColorIndex = 4
Next

But I am at a lost when it comes to accessing the correct cells from the other sheets, and can't wrap my head around how to use a loop to manipulate the formula.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

j0coder

New Member
Joined
Nov 2, 2017
Messages
6
any and all help/suggestions/alternative ways to do this are greatly appreciated
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
So, you are trying to compare like cells on the two sheets (compare A1 on first sheet, to A1 on second sheet, then A2 on first sheet to A2 on second sheet, etc)?
And then you want to highlight the ones that don't match?
Is that correct?

Which sheet would this highlighting be happening on (one or both)?
How many columns are on these sheets what we need to compare?
 

j0coder

New Member
Joined
Nov 2, 2017
Messages
6

ADVERTISEMENT

That is correct, and then the third sheet will contain either the value "true" or "false" depending if the cells of sheet 1 and sheet 2 match. I have the formula that does the trick but I am not sure how to go about looping through the correct cells(based on the other sheets)
 

j0coder

New Member
Joined
Nov 2, 2017
Messages
6
The number of rows will be different every time we will need this to be executed, but the number of columns should be the same each time if that helps
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is it safe to assume that each sheet may have a different number of rows?
What about columns? Are there always a set number of columns?
 

j0coder

New Member
Joined
Nov 2, 2017
Messages
6
The first 2 sheets will have the same number of rows and columns, and ideally match exactly (that's the purpose of this script) Number of columns will be set every time and shouldn't change but the number of rows each day we need to run this will be different
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,935
Office Version
  1. 365
Platform
  1. Windows
OK. I was working on some script that actually looks at the number of columns and rows and each sheet, and take the maximum, and creates that many comparison formulas on the third page.
So this script will populate your third sheet with all the formulas. The only thing you might need to change are the sheet names.
Code:
Sub MyMacro()

    Dim dws1 As Worksheet
    Dim dws2 As Worksheet
    Dim cws As Worksheet
    
    Dim lCol1 As Long
    Dim lCol2 As Long
    Dim lRow1 As Long
    Dim lRow2 As Long
    Dim maxCol As Long
    Dim maxRow As Long
    Dim frm As String
    
'   Designate two data worksheets to compare
    Set dws1 = Sheets("Sheet1")
    Set dws2 = Sheets("Sheet2")
'   Designate comparison sheet
    Set cws = Sheets("Sheet3")

'   Find last rows and columns on two sheets
    lCol1 = dws1.Range("A1").SpecialCells(xlLastCell).Column
    lRow1 = dws1.Range("A1").SpecialCells(xlLastCell).Row
    lCol2 = dws2.Range("A1").SpecialCells(xlLastCell).Column
    lRow2 = dws2.Range("A1").SpecialCells(xlLastCell).Row
    
'   Find maximum column number
    If lCol1 > lCol2 Then
        maxCol = lCol1
    Else
        maxCol = lCol2
    End If
    
'   Find maximum row number
    If lRow1 > lRow2 Then
        maxRow = lRow1
    Else
        maxRow = lRow2
    End If

'   Enter formulas on third sheet
    frm = "=" & dws1.Name & "!RC=" & dws2.Name & "!RC"
    cws.Activate
    cws.Range(Cells(1, 1), Cells(maxRow, maxCol)).FormulaR1C1 = frm
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

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