WorkSheet_Change Issue

[jumbo1979]

My goal is to have the result of:

11:29:30 AM United States 2,575,517 0.1046%
11:29:30 AM Japan 6,013,355 0.2442%
11:29:30 AM Hong Kong 669,088 0.0272%

update every time the data from wrksheet1 refreshes. However with the current VBA in my wrksheet it continues to grow row by row every time the data refreshes. I want three rows that are overwritten each time the data is refreshed. I do not want it to add additional rows. Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long, lRow2 As Long, WF As WorksheetFunction
Set WF = Application.WorksheetFunction
If Not Application.Intersect(Target, Range("D")) Is Nothing Then
With Sheet2
lRow = .Range("A1").CurrentRegion.Rows.Count
.Cells(lRow, 1) = Now
.Cells(lRow, 2) = "United States"
.Cells(lRow, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "USA", Sheet1.Range("D"))
.Cells(lRow, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "USA", Sheet1.Range("D")) / Sheets("Notionals").Range("E16").Value
End With
With Sheet2
lRow2 = lRow + 1
.Cells(lRow2, 1) = Now
.Cells(lRow2, 2) = "Japan"
.Cells(lRow2, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "JAP", Sheet1.Range("D"))
.Cells(lRow2, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "JAP", Sheet1.Range("D")) / Sheets("Notionals").Range("E16").Value
End With
With Sheet2
lRow3 = lRow2 + 1
.Cells(lRow3, 1) = Now
.Cells(lRow3, 2) = "Hong Kong"
.Cells(lRow3, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "HOK", Sheet1.Range("D"))
.Cells(lRow3, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "HOK", Sheet1.Range("D")) / Sheets("Notionals").Range("E16").Value
End With
End If
Set WF = Nothing
End Sub

How can I adjust the current code to just overwrite the three rows every time the data is updated? any help would be much appreciated.

thanks

jumbo

 

[PHV]

lRow = .Range("A1").CurrentRegion.Rows.Count - 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jumbo1979]

PH,

When I add "- 2" to lRow I get an error:

Run-time error '1004':
Application-defined or object-defined error

Any thoughts?

thanks

jumbo

 

[KenPuls]

Hi there,

I usually prefer to test this on live data, but I don't want to try and replicate your workbook here.

If I understand correctly, you want to overwrite rows 1,2 and 3 every time. The way you're set up, you are measuring the count of the current region every time. Add data, and that will grow.

I'm thinking that you may be after this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WF As WorksheetFunction
    Set WF = Application.WorksheetFunction
    If Not Application.Intersect(Target, Range("D:D")) Is Nothing Then
        With Sheet2
            .Cells(1, 1) = Now
            .Cells(1, 2) = "United States"
            .Cells(1, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "USA", Sheet1.Range("D:D"))
            .Cells(1, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "USA", Sheet1.Range("D:D")) / Sheets("Notionals").Range("E16").Value
            .Cells(2, 1) = Now
            .Cells(2, 2) = "Japan"
            .Cells(2, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "JAP", Sheet1.Range("D:D"))
            .Cells(2, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "JAP", Sheet1.Range("D:D")) / Sheets("Notionals").Range("E16").Value
            .Cells(3, 1) = Now
            .Cells(3, 2) = "Hong Kong"
            .Cells(3, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "HOK", Sheet1.Range("D:D"))
            .Cells(3, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "HOK", Sheet1.Range("D:D")) / Sheets("Notionals").Range("E16").Value
        End With
        End If
    Set WF = Nothing
End Sub

Let me know if I'm off base.

Ken Puls, CMA

http://www.excelguru.ca

 

[jumbo1979]

Ken,

This works perfectly. Thanks!

One more question - in the same code, How do I get it to sort descending on column C after all of the data is populated?

thanks

happy jumbo

 

[KenPuls]

HI there,

From the Macro recorder:

    Range("A1:D18").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

You'll need to adapt the range to suit. If you need it dynamic, let me know what column holds the last bit of data.

Cheers,

Ken Puls, CMA

http://www.excelguru.ca