VBA: Compare rows of worksheets

MJAY__77

New Member
Joined
Mar 7, 2019
Messages
10
Hello all together,

I would like to compare the rows of two worksheets with each other.

So, for example worksheet 1 has 8 rows, and worksheet 2 has 5 rows there is a difference of -3.

Now I would like to have a macro which calculate this automatically and put it into a specific cell.

I hope somebody can help me and I wish you a nice sunday :)
 

Some videos you may like

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.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,509
Try:
Code:
Sub mjay()
    Dim lRow1 As Long, lRow2 As Long
    lRow1 = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lRow2 = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    MsgBox lRow1 - lRow2
End Sub
Replace the msgbox line with code to put the value in the desired cell.
 

MJAY__77

New Member
Joined
Mar 7, 2019
Messages
10
Hello mumps,

thank you very much for your answer.

I wasn't precise enough. Is there a possibility to construct the code similar to this one? Like a simple one-line? Because I've got already a big code in this macro, I only need to optimize it.

Range("B31").Select
ActiveCell.FormulaR1C1 = "=(COUNTIF(" & ActualWeek & "R2C7:R50000C7,""820""))-(COUNTIF(" & PreviousWeek & "R2C7:R50000C7,""820""))"
 

MJAY__77

New Member
Joined
Mar 7, 2019
Messages
10
Update:
Is there a possibility to keep it simple with a COUNTA code?
I never used this function, but I read about it in the internet. Then I could do maybe the same like above, but only with the counta function.
Is this possible?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,509

ADVERTISEMENT

Try:
Code:
Range("[COLOR="#FF0000"]B[/COLOR]31") = Sheets("ActualWeek").Range("[COLOR="#FF0000"]B[/COLOR]" & Sheets("ActualWeek").Rows.Count).End(xlUp).Row - Sheets("PreviousWeek").Range("[COLOR="#FF0000"]B[/COLOR]" & Sheets("PreviousWeek").Rows.Count).End(xlUp).Row
Change the column letter (in red) to suit your needs.
 
Last edited:

MJAY__77

New Member
Joined
Mar 7, 2019
Messages
10
So basically the code works well and fits for what I'm looking for.

There is only one issue:
The name of the worksheets change over time.
But I defined in the beginning, that I will ever compare the ActualWeek with the PreviousWeek.

Code:
Dim ActualWeek As String
ActualWeek = Sheets("Settings").Range("F5").Value

Dim PreviousWeek As String
PreviousWeek = Sheets("Settings").Range("F4").Value

So when I put into cell F5 in the worksheet "Settings" the value Week10 this will be my actual week. Normally the VBA should recognize now, what is actual and what is previous week.
So in the end everytime comes "runtime error 9". VBA doesn't find my worksheet.

I hope it's comprehensible.

Best regards
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,509

ADVERTISEMENT

Are you saying that the names of the worksheets are in F4 and F5 of "Settings"?
 

MJAY__77

New Member
Joined
Mar 7, 2019
Messages
10
Yes, that is what I wanted to say.

The name of the worksheet changes with the values of the cells of F4 and F5 in another worksheet "Settings".
Maybe it's a little bit confusing. When it's to difficult to understand, because I'm not able to explain it well enough I have also another solution approach.
But first let's go on with this approach :rolleyes:
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,509
Try:
Code:
Sub Test()
    Dim wk1 As String, wk2 As String
    wk1 = Sheets("Settings").Range("F4").Value
    wk2 = Sheets("Settings").Range("F5").Value
    Range("B31") = Sheets(wk2).Range("B" & Sheets(wk2).Rows.Count).End(xlUp).Row - Sheets(wk1).Range("B" & Sheets(wk1).Rows.Count).End(xlUp).Row
End Sub
 

MJAY__77

New Member
Joined
Mar 7, 2019
Messages
10
Doesn't work. But big thanks for your effort! There is again "runtime error 9" so he's lost anywhere in a string and doesn't find my active worksheet. But nevermind!

Actually I think it could be solved way easier.
In column G of all my worksheets are only written down 800 or 810 (written as text format). There are no other possibilites to put in.
Now I had the idea to use a COUNTIFS function. Sum up all 800 & 810 in both worksheets and calculate the difference of it.

Code:
ActiveCell.FormulaR1C1 = "=(COUNTIFS(" & ActualWeek & "R2C7:R50000C7,""800""," & ActualWeek & "R2C7:R50000C7,""810""))-(COUNTIFS(" & PreviousWeek & "R2C7:R50000C7,""800""," & PreviousWeek & "R2C7:R50000C7,""810""))"

So far so good.

In general the code works and there is no failure in it on the first look.
Also I'm able to execute the code, but there is everytime the value 0 as solution.
Independently if there is a delta of +5 or -5, or +18 or -15.
I also tried to set the format to numbers, but it doesn't work.
Maybe this is a little bit easier, I don't know :confused::)

Thanks a lot in advance.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,491
Messages
5,523,255
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top