Robert Casaletta
asked on
Target.Value Runtime Error
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you tell what the error description is?
For your line:
If InStr(1, temp, Cells(AB, 29).Value) Then
Try
If InStr(1, temp, Cells(AB, 29).Value) > 0 Then
Chris
If InStr(1, temp, Cells(AB, 29).Value) Then
Try
If InStr(1, temp, Cells(AB, 29).Value) > 0 Then
Chris
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?
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.
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.
ASKER
I have been traveling and unable to try your solutions...Thank Uou all...I will try them today...