redu code for additional instring debugging

Posted on 2012-08-28
Last Modified: 2012-08-29
excel 2010 vba

If InStr(1, Target.Address, str) > 0 And Target.Value <> "" Then
            'we are in the entry column
            If getdata(Target) <> "" Then
                MsgBox getdata(Target)
            End If


str = <is usually a value of an Alpha character>

if the str is instring in the target.address then routine gets ran

so if the address is   $I$52

and the "str = I"  THEN the routine gets ran.

my problem.

sometimes the target.address is a double Alpha like   AI52....IN THIS CASE I DO NOT WANT THE ROUTINE TO RUN SO END THE IF STATEMENT

BUT THE CODE IS FINDING THE  "I" inside the target address

I need to get around this double alpha problem.

Question by:fordraiders
    LVL 8

    Expert Comment

    by:Elton Pascua
    Did you want the sub to run only when it's typed in Column I? If so, the code below should work for you. Otherwise, is there any reason why you are searching for the specific string of the address?

        If Target.Column = 9 And Target.Value <> "" Then
            MsgBox ("Do Something")
        End If

    Open in new window

    LVL 3

    Author Comment

    no str could be any alpha value...or,,,str needs to be in position 1

    IF str were  "A" AND THE target was "A3" then run the routine..

    LVL 8

    Accepted Solution

    How about this?

    If UCase(Left(Replace(Target.Address, "$", ""), 1)) = UCase(str) And Target.Value <> "" Then

    Open in new window

    LVL 10

    Assisted Solution

    If you change your IF to the following, it will work:
    If Target.Address = "$" & str & "$" & Target.Row Then

    Open in new window

    LVL 85

    Assisted Solution

    by:Rory Archibald
    If not intersect(target.worksheet.columns(str), Target) is nothing And Target.Value <> "" Then

    Open in new window

    should work.
    LVL 3

    Author Closing Comment

    Thanks to all ! perfect

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now