If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

[frog5]

Hi,

The following works great, with one exception, when the target.address is blank (nothing) the macro does not run (of course).

What I need, is for the macro to run in both instances, where the target.address is changed with existing data already entered, AND when the target address was originally blank, but new data was entered.

Here is the code...

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that when any of the ranged of cells are changed
    ' triggers the following macros to run.
    
    Set KeyCells = Range("G15:G5006")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
                
        If ActiveCell.Offset(-1, -2) = "CashAcct" Then
            Call Cash
            Else


        If ActiveCell.Offset(-1, -2) = "BestBuyVisa" Then
            Call BestBuy
            Else
        
        If ActiveCell.Offset(-1, -2) = "SlateVisa" Then
            Call ChaseSlate
            Else
            
        If ActiveCell.Offset(-1, -2) = "BoAMC" Then
            Call BoA
            Else

... and so forth.

 

[frog5]

Mike,

So, changing it to lower case, did cause it to fire (of course). Thank you.

Still, it falls short two columns when there is nothing in the field and it is a new entry.

Setting up the vbNullString, any sample code?

I presume, I could have two separate sets of macros, one that is 2 columns "moreso" and one that is not.

Of course, I am sure you could come up with a much better, simpler approach. Any code samples?

 

[mikerickson]

What do you mean "falls short two columns"

 

[frog5]

Meaning, instead of it landing on the correct column, say, AAZ, it lands on AAX (two columns shy of what it is supposed to).

Because it doesn't do that when the SAME macro is ran when the field's value is changed (as opposed to inputted for the first time), it is perplexing.

Here is the code, as it stands right now...

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        
        Select Case LCase(Target.Offset(0, -2).Cells(1, 1).text)

 

[frog5]

In looking at the pic (page one, last comment (URL link), the macro is set to fire when (say) G1 is changed.

The pic shows, "Check from Idaho" is in the field. Say it is changed to "b". Then, the macro reads the contents in E1 "SlateVisa", and based on that content, matches the case, and call that macro, which in turn simply moves the cursor a specified number of columns to the right as shown in the called macro code:

Sub ChaseSlate()
' ChaseSlate Macro
    ActiveCell.Offset(-1, 176).Range("A1").Select
End Sub

However, when G9 (a existing blank row) is inputed, the same macro is called, based on the contents of E9, say cashacct is entered into E9, and though the cursor does move to the right, it is 2 columns shy of where it is supposed to land.

I am not familiar with

Select Case LCase(Target.Offset(0, -2).Cells(1, 1).text)
So I do not know what

cells(1, 1) is doing.

 

[mikerickson]

If what you want is

If Target contains "", then do OneThing
If Target contains something else, the do the macro indicated by the cell two to the left of Target

Just wrap the whole Select Case in a If

If Target.Cells(1,1).Text = vbNullString then
    Call OneThing
Else
    Select Case LCase(Target.Offset(0, -2).Cells(1,1).Text)
        ' etc.
    End Select
End If