running counter between 2 sheets

[pauliec]

hello all, I'm working with 2 sheets, Before and After to track the dollar amount of changes made. basically in the before, if an operator under discloses an item and it's corrected by a reviewer in the after sheet, I want to track the cumulative changes made.

I basically need:

for the range from y3 through BM
if the before cell value < after cell value then
counter = counter + (before cell value-after cell value)
then display the counter value in a cell on sheet 3 or a message box.

I keep getting messed up on the syntax though. I have it like this:
Set searchrange = ws1.Range("y3:BM" & ws1LastRow)
Set ws2 = Sheets("Sheet2")
With ws2
ws2lastRow = .Range("Y" & .Rows.count).End(xlUp).Row
For i = 2 To ws2lastRow
counter = counter + (Sheet1.Cells("Y" & i).Value - Sheet2.Cells("Y" & i).Value)
Next i
End With

Sheet3.Cells(1, 1) = counter
End Sub


can someone please point me in the right direction? thank you for your help!!
paul

 

[portews]

Try this. Put this in the After worksheet code page.

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#00ff00]'if the change is greater than row 2 and the column is greater then Y and less than BM[/COLOR]
If Target.Row > 2 And Target.Column > 24 And Target.Column < 66 Then
[COLOR=#00ff00]    'if whats in the cell now is less than what's on the same cell on the Before sheet, increment Sheet3, range A1 by one.[/COLOR]
    If Target > Sheets("Before").Range(Target.Address) Then Sheets(3).Range("A1") = Sheets(3).Range("A1") + 1
End If
End Sub

 

[pauliec]

thank you.....I'm sorry I'm looking for a way to track the actual changes....so if 50 was disclosed (on before) but it's really 100 (on after) I want the counter to add 50 to a running total. I'll use it to track daily changes. thank you again for the stab though!

Try this. Put this in the After worksheet code page.

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#00ff00]'if the change is greater than row 2 and the column is greater then Y and less than BM[/COLOR]
If Target.Row > 2 And Target.Column > 24 And Target.Column < 66 Then
[COLOR=#00ff00]    'if whats in the cell now is less than what's on the same cell on the Before sheet, increment Sheet3, range A1 by one.[/COLOR]
    If Target > Sheets("Before").Range(Target.Address) Then Sheets(3).Range("A1") = Sheets(3).Range("A1") + 1
End If
End Sub

 

[portews]

A counter usually means add one each time. You are looking to sum the additional amounts above estimate, correct?

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#00ff00]'if the change is greater than row 2 and the column is greater then Y and less than BM[/COLOR]
If Target.Row > 2 And Target.Column > 24 And Target.Column < 66 Then
[COLOR=#00ff00]    'if whats in the cell now is more than what's on the same cell on the Before sheet, add the difference to Sheet3, range A1.[/COLOR]
    If Target > Sheets("Before").Range(Target.Address) Then Sheets(3).Range("A1") = Sheets(3).Range("A1") + [COLOR=#ff0000]Target - Sheets("Before").Range(Target.Address)[/COLOR]

End If [COLOR=#333333][I]End Sub[/I][/COLOR]

 

[pauliec]

yes....I've been researching some more and i think i effectively need an array.....that is, the before and the after have the exact same number of rows and columns.....really comparing each category cell in before to its associated cell in after and subtracting one from the other if after > before....could store the entire new array into a reconciled sheet and sum it that way too. just not quite sure yet how to do an array