Compare two sheets, identify values, calculate delta - Pls help

[Hannes]

Hi,

I need to develop a macro which has to first identify and then calculate
between two sheets. I just started to learn to program macros so please help
me with this one (as it seems far to complex to do that on my own).

Details:
The macro starts in sheet1 in row 3 takes the values in this row of column
A, C, D and compares them with sheet2 with the same columns but any row. If
the values match the macro should go on and subtract the values in Column M
in sheet2 from sheet1 and output the result in Column N in row 3 in sheet1.
If the macro does not find a match of row 3 of sheet1 in sheet2 it should go
on to the next row in sheet1 and start with the comparison of column A, ...
and so on.

Many thanks for any support!!

 

[Per Jessen]

Hi

Try this:

Sub Compare_Calculate()
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

sRow = 3
eRow = Range("A3").End(xlDown).Row
For r = sRow To eRow
d1 = sh1.Cells(r, "A").Value
d2 = sh1.Cells(r, "C").Value
d3 = sh1.Cells(r, "D").Value

Set f = sh2.Columns("A").Find(what:=d1)
If Not f Is Nothing Then
If Range(f.Address).Offset(0, 2) = d2 And _
Range(f.Address).Offset(0, 3) = d3 Then
Delta = sh1.Cells(f.Row, "M").Value - sh2.Cells(f.Row, "m")
Cells(f.Row, "N") = Delta
End If
End If
Next
End Sub

Regards,
Per

 

[Hannes]

Unfortunately it doesn't work.
I get the error at "Delta = sh1.Cells...": False compilation - variable or
procedure instead of module expected.

 

[Per Jessen]

Hi

I think it's because the line miss a ".Value" at the end. Try this:

Delta = sh1.Cells(f.Row, "M").Value - sh2.Cells(f.Row, "m").Value

Best regards,
Per

 

[Hannes]

Hi Per,

still the same error message. Any ideas?

Thanks!

 

[Per Jessen]

Hi

I bet you have a module named "Delta"

Change the module name or the variable name.

cDelta = sh1.Cells(f.Row, "M").Value - sh2.Cells(f.Row, "m")
Cells(f.Row, "N") = cDelta

Regards,
Per

 

[Hannes]

Hi Per,

you were right about the module name ;-) The macro runs BUT: currently just
the values of column A have to match in both sheets in a row for the delta
calculation. So if the values of column C and D mismatch the macro calculates
the delta anyway.
I need the macro just to calculate the delta if all values in columns (A, C,
D) match between the two sheets. The values of column A, C, D in a row should
serve as identifier. So if even only one value differs the macro should
consider a mismatch and go to the next row.

Many thanks!!

Regards,
Hannes

 

[Per Jessen]

Hi again

I have testet the macro again, and on my test sheets it's working as you
require.

Maybe column N should be cleared at the beginning at the macro.

To do this add this line after set sh2=....

sh2.range("N3:N65536").clearcontents

Hopes it helps.

Best regards,
Per