VBA: Compare rows of worksheets

[MJAY__77]

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

 

[mumps]

Try:

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]

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]

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]

Try:

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.

 

[MJAY__77]

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.

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]

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

 

[MJAY__77]

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

 

[mumps]

Try:

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]

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.

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

Thanks a lot in advance.