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

Asked By Hanne on 06-Aug-08 06:45 AM
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 replied on 06-Aug-08 07:17 AM
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
Hanne replied on 06-Aug-08 07:40 AM
Unfortunately it does not work.
I get the error at "Delta = sh1.Cells...": False compilation - variable or
procedure instead of module expected.
Per Jessen replied on 07-Aug-08 04:36 AM
Hi

I think it is 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
Hanne replied on 07-Aug-08 08:27 AM
Hi Per,

still the same error message. Any ideas?

Thanks!
Per Jessen replied on 07-Aug-08 08:48 AM
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
Hanne replied on 07-Aug-08 11:16 AM
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 replied on 07-Aug-08 04:55 PM
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