?
Solved

redu code for additional instring debugging

Posted on 2012-08-28
6
Medium Priority
?
267 Views
Last Modified: 2012-08-29
excel 2010 vba


<CODE>
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

<CODE>


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.

Thanks
fordraiders
0
Comment
Question by:Fordraiders
6 Comments
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38343797
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

0
 
LVL 3

Author Comment

by:Fordraiders
ID: 38343821
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..

$a$3
0
 
LVL 8

Accepted Solution

by:
Elton Pascua earned 668 total points
ID: 38343964
How about this?

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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Assisted Solution

by:tdlewis
tdlewis earned 668 total points
ID: 38344098
If you change your IF to the following, it will work:
If Target.Address = "$" & str & "$" & Target.Row Then

Open in new window

0
 
LVL 85

Assisted Solution

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

Open in new window

should work.
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 38345324
Thanks to all ! perfect
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

864 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