Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Sheets values in two colums | Excel Worksheet Functions | |||
compare contents between sheets and identify matching values | Excel Programming | |||
Compare values in multiple sheets | Excel Programming | |||
How do I compare two values to calculate a total? | Excel Worksheet Functions | |||
HOW DO I COMPARE TWO SHEETS AND IDENTIFY WHICH CELLS DIFFER | Excel Discussion (Misc queries) |