Problem executing simple operations ("if-then, "with") on existing sheet with VBA

[crr]

Hello all VBA Geniuses!
I have been lurking around on this forum for awhile, and it has been a tremendous help for me in understanding and utilizing VBA in my Excel Documents.
I have come across a situation that I am having trouble with, and cannot seem to find the answer. I hope you can help.

I have a worksheet (Sheet 7) with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count < 1 Then Exit Sub
On Error Resume Next
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G:G"), Unique:=True

Cells.Columns.AutoFit
End Sub

I have the following code in Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Offset(0, -1).Value = "TOELINK" Then
On Error Resume Next
With Sheet7
With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Cells
.Offset(0, 1) = Sheet1.Range("A5").End(xlToRight)
.Offset(0, 2) = Sheet1.Range("A6").End(xlToRight)
.Offset(0, 3) = Sheet1.Range("A4").End(xlToRight)
End With
End With
End If
Cells.Columns.AutoFit
End Sub

So when I enter a value in "B2" on Sheet2, It records the target cell in "A2", and then some values from Sheet1 in the following columns.

So the current code in Sheet7 copies the unique entries from column A into column G. The last entry in column G, is the active part.

I wanted to put a condition in column H, so that the last corresponding cell would read "Active". (If there is a value in G5 and G6 is "", then H5 would show active. In addition it would make the previous entry in H now read "Obsolete" (ie: H4)

This is leading me to then trying to do some "=Sumif" operations based on the conditions in column A, D, and G.

I have tried to do both of these operations independently of one another, with no luck.

I should have prefaced this question by indicating that I am by no means a VBA programming whiz. Generally I find some code that kinda does what i want, and I can manipulate it to do what I want. Writing from scratch generally is very basic, but effective. I could probably do a better job for error handling and a host of other things, but I am generally the only one who will be using the sheets that I am creating. I have been able to do everything that i want the sheet to do, by writing equations, but I am interested in learning more about VBA, as well as keeping the workbook very tidy so other cannot "screw it up".

Anyhow, I'm down off the soapbox now. Any help is greatly appreciated.

Sorry!
Excel 2010, Windows XP

 

[mole999]

i'm confused

So when I enter a value in "B2" on Sheet2, It records the target cell in "A2", and then some values from Sheet1 in the following columns.

in A2 =Sheet2!B2

(If there is a value in G5 and G6 is "", then H5 would show active. In addition it would make the previous entry in H now read "Obsolete" (ie: H4)

H5 =if(OR(G5="",G6=""),"active","")

are those the sort of things you want ?

 

[crr]

Thanks for having a look!
Let me try to clarify.
The entry I put into "B2" on Sheet2, then gets copied to "A2" on Sheet7.
Every time I enter a value into "B2" (It can be the same entry, or a new, unique entry), it will now copy into "A3", then "A4" etc.

The problem seems to be that when I put in either the "If then" or "Sumif" in the above code after the following line:
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G:G"), Unique:=True

Nothing happens.

To answer your question, that is sort of what I am trying to do.
But G5 will have a value in it; it will not be blank, but G6 will be empty, meaning that G5 is the last entry, and is therefore active.

 

[AlphaFrog]

In your original code in both procedures you have...
On Error Resume Next

This is, generally speaking, not a good idea.

1. If you have an error, you should handle it some how.
2. If you are typing in new code and make a mistake, your macro will do nothing with the new code. It's ignored and you wont know why.

I'm not sure I understand all of what you want to do, but give something like this a try...

Sheet2

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$2" Then  'Test if the change occurred in cell B2
    ' If Target.Column = 2 Then  'Test if the change occurred in column B
    
        If Target.Offset(0, -1).Value = "TOELINK" Then
        
            With Sheet7
                With .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
                    .Value = Target.Value
                    .Offset(0, 1) = Sheet1.Range("A5").End(xlToRight)
                    .Offset(0, 2) = Sheet1.Range("A6").End(xlToRight)
                    .Offset(0, 3) = Sheet1.Range("A4").End(xlToRight)
                End With
            End With
        
        End If

        Cells.Columns.AutoFit
    
    End If

End Sub

Sheet7

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then   ' Only copy uniques when the change is to column A
    
        Application.EnableEvents = False
        
            ' Copy uniques to column G
            Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G:G"), Unique:=True
            
            ' Add "Obsolete" to column H and "Active" to the last value in column
            Range("H2", Range("G" & Rows.Count).End(xlUp).Offset(, 1)).Value = "Obsolete"
            Range("G" & Rows.Count).End(xlUp).Offset(, 1).Value = "Active"
        
        Application.EnableEvents = True
        
        Cells.Columns.AutoFit
        
    End If

End Sub

 

[crr]

AlphaFrog-
THANK YOU SO MUCH!
So far this has worked exactly as I intended.
I appreciate your feedback as well, as sometimes, I believe I am doing the right thing, but am unsure. The note about "On error resume next" is appreciated!

The only reason I say that "so far it is working" is because I am taking things in steps to fully develop my sheet.
If I put too much in at once, I have a hard time figuring out where errors are.
You solution will no doubt, give me more in the arsenal for figuring out new problems.

Thanks again!

 

[AlphaFrog]

I'm glad it worked for you.

If you will be making additional changes, you should take note of this...
"Without proper coding, your event procedures can end up in infinite recursive loops."

For instance, if your WorkSheet_Change procedure for say Sheet7 makes a change to Sheet7, then it will trigger itself to run again. Then it could make the change to Sheet7 again and then trigger itself again.... Infinitum.

You can Suspend\Enable events with code like this...

    Application.EnableEvents = False   [COLOR="Green"] ' Suspend events from triggering
        '
        ' Make changes to sheet here
        '[/COLOR]
    Application.EnableEvents = True     [COLOR="Green"]' Re-enable events to trigger[/COLOR]

This site explains it better
http://www.cpearson.com/excel/Events.aspx
Scroll down to Preventing Event Loops

In the code I provided, I tested if the Worksheet_Change occurs in a specific cell or column and then the code doesn't make any changes to those specific cells or columns. No infinite loop is created.

Just something you should be aware of.

 

[crr]

Many Thanks for the hints!
I have found myself in these loops already.....
Causes me to lunge for the 'esc' key.

I've read Walkenbach's EXcel VBA for dummies. I think I need to get ahold of the Bible now.

Thanks again for your help.