?
Solved

Target.Value Runtime Error

Posted on 2014-01-17
6
Medium Priority
?
213 Views
Last Modified: 2014-01-23
I recently upgraded to Excell 2010 from Excell 2003 and I am getting a runtime error in the 2010 workbook on VBA code that works fine in the 2003 workbook. The error occurs at the Target.Value line of the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 8 And Target.Row = 25 Then
temp = Target.Value
For AB = 66 To 7000
If InStr(1, temp, Cells(AB, 29).Value) Then
Target.Value = Cells(AB, 28).Value
Exit For
End If
Next
End If

Any clues on what to do? I have tried to add DoEvents at the beginning of the statement with no luck.
0
Comment
Question by:castlerj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39790490
Hi,

pls try

If Target.Address = "$H$25" Then
    temp = Target.Value
    For AB = 66 To 7000
        If InStr(1, temp, Cells(AB, 29).Value) Then
            Application.EnableEvents = False
            Range(Target.Address).Value = Cells(AB, 28).Value
            Application.EnableEvents = True
            Exit For
        End If
    Next
End If

Open in new window

Regards
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39790497
Can you tell what the error description is?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39790503
For your line:

If InStr(1, temp, Cells(AB, 29).Value) Then

Try

If InStr(1, temp, Cells(AB, 29).Value) > 0 Then

Chris
0
Technology Partners: 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 49

Expert Comment

by:Martin Liss
ID: 39790647
Your code works fine for me in Excel 2010 using some simple data that I made up. Can you post the actual workbook, or a sample workbook that illustrates the problem?
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39793349
Look for the problem in the data types. The Instr function handles Strings. Temp, since it is not dimensioned, is a Variant. There is no guarantee that the value assigned to it will be  a String.. Similarly, Cells(AB, 29).Value is a Variant without assurance of its being of VarType(vbString). So, there is a great chance of comparing apples with oranges.
Start by defining the types of your variables.
Dim Temp As String
Dim AB As Long
Then convert Cells(AB, 29).Value into a String before processing it in the Instr function.
If Instr(1, Temp, CStr(Cells(AB, 29).Value)) Then

This may not cure your problem because between row 66 and row 7000 a lot of different values are assigned to Temp, and only one of these may cause the problem. The difference you will find is that the error message will be more specific. Most important, when an error occurs press the Debug button in the message box and look at the highlighted line of code. Also hover your cursor over the variable AB. This will display its current value, pointing you, in fact, at the cell that is at the source of the error.
0
 

Author Comment

by:castlerj
ID: 39804117
I have been traveling and unable to try your solutions...Thank Uou all...I will try them today...
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

765 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