cell address in rng not working

Posted on 2012-08-27
Last Modified: 2012-08-28
excel 2003 vba

I code that should be picking a cell.value for a range...

but not taking the value:

Set rng = Range(Target.Address)  

For Each cell In rng
       xsearch = "SELECT AlternatesPL.ALT FROM AlternatesPL Where AlternatesPL.Item=""" & cell.Value & """;"
in this case the target.address  could be "I19"

cell.value is coming up as nothing.

Question by:fordraiders
    LVL 33

    Expert Comment

    Where does Target come from?

    PS If Target is a range you could just use Set rng = Target, or even use Target in the For Each.
    LVL 3

    Author Comment

    Private Sub xlApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    being passed...this is an add-in app,
    LVL 33

    Accepted Solution

    Why not try just using Target like I suggested.
    Set rng = Target

    Open in new window

    This will ensure that you are looking at the correct range on the correct sheet.

    With the original code you would have been looking at the correct range on the active sheet, which might not be the correct sheet.
    LVL 3

    Author Closing Comment

    perfect Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now