Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code not working after worksheet change.
I have been using the following code for a while. Recently, I deleted the
rows between 2 and 36 and then inserted rows back in so that the sheet looks exactly the same. However, the code now only works for rows 2 and 36 and seems to skip over the new rows 4 through 34. If anyone knows why this might be happening and what I need to do about it, I would really appreciate your help. Thanks. Code follows: Sub LastEvent() Dim fWks As Worksheet Dim tWks As Worksheet Dim iRo As Long Dim FirstRow As Long Dim LastRow As Long Dim FirstCol As Long Dim LastCol As Long Dim myRng As Range Dim myCell As Range Dim myValToCopy As Variant Set fWks = Worksheets("Tracker") Set tWks = Worksheets("Stucon Weekly Report") With fWks FirstRow = 2 LastRow = 36 FirstCol = .Range("G1").Column LastCol = .Range("AD1").Column For iRo = FirstRow To LastRow Step 2 Set myRng = .Range(.Cells(iRo, FirstCol), .Cells(iRo, LastCol)) If myRng.Cells.Count = Application.Count(myRng) _ Or Application.Count(myRng) = 0 Then Set myCell = Nothing Else If IsEmpty(.Cells(iRo, LastCol)) = False Then Set myCell = .Cells(iRo, LastCol) Else If IsEmpty(.Cells(iRo, LastCol - 1)) = False Then Set myCell = .Cells(iRo, LastCol - 1) Else Set myCell = .Cells(iRo, LastCol).End(xlToLeft) End If End If End If If myCell Is Nothing Then myValToCopy = "" Else myValToCopy = .Cells(1, myCell.Column) End If tWks.Cells((iRo / 2) + 3, 6) = myValToCopy Next iRo End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet change event not working | Excel Discussion (Misc queries) | |||
change the name of a worksheet/update VBA code | Excel Discussion (Misc queries) | |||
Worksheet change suddenly not working | Excel Programming | |||
Worksheet change code | Excel Programming | |||
Enable/Disable Worksheet Change Event code | Excel Programming |