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

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!!

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

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" skrev i meddelelsen
...
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!!

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

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

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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!!

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

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" skrev i meddelelsen
...
Unfortunately it doesn't work.
I get the error at "Delta = sh1.Cells...": False compilation - variable or
procedure instead of module expected.

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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!!

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

Hi Per,

still the same error message. Any ideas?

Thanks!


"Per Jessen" wrote:

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" skrev i meddelelsen
...
Unfortunately it doesn't work.
I get the error at "Delta = sh1.Cells...": False compilation - variable or
procedure instead of module expected.

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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!!

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

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" skrev i meddelelsen
...
Hi Per,

still the same error message. Any ideas?

Thanks!


"Per Jessen" wrote:

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" skrev i meddelelsen
...
Unfortunately it doesn't work.
I get the error at "Delta = sh1.Cells...": False compilation - variable
or
procedure instead of module expected.

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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!!

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

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" wrote:

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" skrev i meddelelsen
...
Hi Per,

still the same error message. Any ideas?

Thanks!


"Per Jessen" wrote:

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" skrev i meddelelsen
...
Unfortunately it doesn't work.
I get the error at "Delta = sh1.Cells...": False compilation - variable
or
procedure instead of module expected.

"Per Jessen" wrote:

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" skrev i meddelelsen
...
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!!

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

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

"Hannes" skrev i meddelelsen
...
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