Btukfyl

I have a macro that I have tested on a development workbook. This macro runs successfully and provides the expected results. When I copy this macro to the production version of the workbook, the macro aborts for different reasons. When I step through the macro on debug, the code does nor execute as expected. For example, in the production version, the IF statement testing the date executes, followed by the first .Cells statement then the macro aborts.

No clue on why. Any ideas would be appreciated.

Private Sub Worksheet_Calculate()

Dim Cost_Per_day

Dim COST_kg

Dim AVG_SALES_PRICE

Dim COST_NET_PURCHASE

Dim PROFIT_GROSS

Dim PROFIT_NET

Dim PROFIT_NET_X

Dim Flag_set



Dim dtmTime As Date

Dim Rw As Long



'If Critical Cells change, move contents to Log sheet



Dim Xrg As Range

Set Xrg = Range("E5:I11")

If Not Intersect(Xrg, Range("E5:I11 ")) Is Nothing Then







dtmTime = Now()

Cost_day = Worksheets("FEED_ANALYSIS").Range("E7").Value

COST_kg = Worksheets("FEED_ANALYSIS").Range("F7").Value

AVG_SALES_PRICE = Worksheets("FEED_ANALYSIS").Range("I5").Value

COST_NET_PURCHASE = Worksheets("FEED_ANALYSIS").Range("G11").Value

PROFIT_GROSS = Worksheets("FEED_ANALYSIS").Range("I7").Value

PROFIT_NET = Worksheets("FEED_ANALYSIS").Range("I8").Value

PROFIT_NET_X = Worksheets("FEED_ANALYSIS").Range("I9").Value







Rw = Sheets("LOG").Range("A" & Rows.Count).End(xlUp).Row + 1



With Sheets("LOG")

    datcomp = .Cells(Rw - 1, 1)



   ' if the previous entry date is the same as the current date, do not create the entries... one entry per day`



    If Year(datcomp) = Year(dtmTime) And Month(datcomp) = Month(dtmTime) And Day(datcomp) = Day(dtmTime) Then GoTo NoUpd



    .Cells(Rw, 1) = dtmTime

    .Cells(Rw, 2) = Cost_Per_day

    .Cells(Rw, 3) = COST_kg

    .Cells(Rw, 4) = AVG_SALES_PRICE

    .Cells(Rw, 5) = COST_NET_PURCHASE

    .Cells(Rw, 6) = PROFIT_GROSS

    .Cells(Rw, 7) = PROFIT_NET

    .Cells(Rw, 8) = PROFIT_NET_X

    .Cells(Rw, 11) = .Cells(Rw - 1, 1)

 NoUpd:

 End With



 End If



 End Sub`

Assumption: Data is being feed into sheet FEED_ANALYSIS range E5:I11.
Following Problem guessed:

1. After first change in any cell (or calculate event) of FEED_ANALYSIS, current date is being added to col A in sheet LOG with variable dtmTime (and being treated as datcomp in next event). So further update of LOG from cell changes in FEED_ANALYSIS are prevented as if clause is comparing dtmTime with datcomp and branching to NoUpd:.


2. I think the code will be fired always on the calculation event of the worksheet. If Not Intersect(Xrg, Range("E5:I11 ")) Is Nothing Then will be always true.


3. Typo of Cost_Per_day and Cost _day may be throwing error at .Cells(Rw, 2) = Cost_Per_day
   

if the above assumptions are correct, may try the code in FEED_ANALYSIS. Tried to keep modifications minimal.

Option Explicit       'added

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cost_Per_day

Dim COST_kg

Dim AVG_SALES_PRICE

Dim COST_NET_PURCHASE

Dim PROFIT_GROSS

Dim PROFIT_NET

Dim PROFIT_NET_X

Dim Flag_set

Dim dtmTime As Date

Dim datcomp As Date   'added

Dim Rw As Long

Dim LastRw As Long    ' added

Dim PrvRw As Long     'added

'If Critical Cells change, move contents to Log sheet



Dim Xrg As Range

Set Xrg = Range("E5:I11")

If Not Intersect(Target, Xrg) Is Nothing Then



dtmTime = Now()

Cost_Per_day = Worksheets("FEED_ANALYSIS").Range("E7").Value  ' Cost_day changed to Cost_Per_day as per Dim

COST_kg = Worksheets("FEED_ANALYSIS").Range("F7").Value

AVG_SALES_PRICE = Worksheets("FEED_ANALYSIS").Range("I5").Value

COST_NET_PURCHASE = Worksheets("FEED_ANALYSIS").Range("G11").Value

PROFIT_GROSS = Worksheets("FEED_ANALYSIS").Range("I7").Value

PROFIT_NET = Worksheets("FEED_ANALYSIS").Range("I8").Value

PROFIT_NET_X = Worksheets("FEED_ANALYSIS").Range("I9").Value





LastRw = Sheets("LOG").Range("A" & Rows.Count).End(xlUp).Row + 1

PrvRw = LastRw - 1

With Sheets("LOG")

    datcomp = .Cells(PrvRw, 1)



   ' if the previous entry date is the same as the current date then

   ' choose previous entry row to update other data

   ' else

   ' Chose last row  for new entry

   ' But this approach will not work if data is not enetered  for some unforeseen

   ' reason before 23:59 hrs of currect date i.e dtmTime = Now()

   ' Better to use datetime from a manually entered cell in sheet "FEED_ANALYSIS"

   ' with some validation



    If Year(datcomp) <> Year(dtmTime) Or Month(datcomp) <> Month(dtmTime) Or Day(datcomp) <> Day(dtmTime) Then

    Rw = LastRw

    .Cells(Rw, 1) = dtmTime

    Else

    Rw = PrvRw

    End If



    .Cells(Rw, 1) = dtmTime

    .Cells(Rw, 2) = Cost_Per_day

    .Cells(Rw, 3) = COST_kg

    .Cells(Rw, 4) = AVG_SALES_PRICE

    .Cells(Rw, 5) = COST_NET_PURCHASE

    .Cells(Rw, 6) = PROFIT_GROSS

    .Cells(Rw, 7) = PROFIT_NET

    .Cells(Rw, 8) = PROFIT_NET_X

    .Cells(Rw, 11) = .Cells(Rw - 1, 1)

End With

End If

End Sub