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

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" <(E-Mail Removed)> skrev i meddelelsen
newsD9B52BC-8E37-4499-A0B8-(E-Mail Removed)...
> 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!!

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" <(E-Mail Removed)> skrev i meddelelsen
> newsD9B52BC-8E37-4499-A0B8-(E-Mail Removed)...
> > 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!!
>
>

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" <(E-Mail Removed)> skrev i meddelelsen
news5EFC3B8-3A00-4683-8304-(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i meddelelsen
>> newsD9B52BC-8E37-4499-A0B8-(E-Mail Removed)...
>> > 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!!
>>
>>

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" <(E-Mail Removed)> skrev i meddelelsen
> news5EFC3B8-3A00-4683-8304-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> skrev i meddelelsen
> >> newsD9B52BC-8E37-4499-A0B8-(E-Mail Removed)...
> >> > 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!!
> >>
> >>
>
>

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" <(E-Mail Removed)> skrev i meddelelsen
news:286CC656-0DFA-44F9-85E4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i meddelelsen
>> news5EFC3B8-3A00-4683-8304-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> skrev i meddelelsen
>> >> newsD9B52BC-8E37-4499-A0B8-(E-Mail Removed)...
>> >> > 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!!
>> >>
>> >>
>>
>>

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" <(E-Mail Removed)> skrev i meddelelsen
> news:286CC656-0DFA-44F9-85E4-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> skrev i meddelelsen
> >> news5EFC3B8-3A00-4683-8304-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> skrev i meddelelsen
> >> >> newsD9B52BC-8E37-4499-A0B8-(E-Mail Removed)...
> >> >> > 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!!
> >> >>
> >> >>
> >>
> >>
>
>

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" <(E-Mail Removed)> skrev i meddelelsen
news:9AC2966A-8D55-4805-AFDC-(E-Mail Removed)...
> 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
>
>
>