Click here to Skip to main content
14,691,952 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am receiving the Run-time error 1004 Application defined or object defined error on the first line of code where I am trying to add a vlookup. It is about 5 lines from the bottom. I appreciate any insight on what is driving it. I am under a tight deadline to finish.


Sub Karen()

Worksheets("Paste Data Here").Select

Dim Colnum As Integer
Dim prefix As String
Dim suffix As String
Dim x As Long



For x = 1 To 100
If UCase(Cells(1, x)) = "CASE CODE" Then
Colnum = x - 1 'The column to the left Case Code in this example.  It would make sense to check and make sure it isn't the first column or an error will occur
Exit For
End If
Next

For x = 1 To 60000

    If Mid(Range("T1:T60000").Cells(x), 6, 1) = "-" Then

        prefix = Mid(Range("T1:T60000").Cells(x), 1, 5)
    End If

            If Cells(x, Colnum + 1) <> "" And Len(Trim(Range("D1:D60000").Cells(x))) = 5 Then
             Cells(x, Colnum) = CStr(prefix & Cells(x, Colnum + 1))

                If Mid(Range("D1:D60000").Cells(x), 6, 1) = "-" Then
                    suffix = Mid(Range("D1:D60000").Cells(x), 7, 5)
                    Cells(x, Colnum) = CStr(prefix & suffix)
                        Else: Cells(x, Colnum) = CStr(prefix & Cells(x, Colnum + 1))

                            If Range("D1:D60000").Cells(x) = "**********" Then
                                Cells(x, Colnum) = CStr(Cells(x, Colnum + 1))
                            End If
                End If

            End If

Next

For x = 1 To 60000

    If Mid(Range("T1:T60000").Cells(x), 6, 1) = "-" Then

        prefix = Mid(Range("T1:T60000").Cells(x), 1, 5)
    End If

            If Cells(x, Colnum + 1) <> "" And Len(Trim(Range("D1:D60000").Cells(x))) = 4 Then
             Cells(x, Colnum) = CStr(prefix & "0" & Cells(x, Colnum + 1))


            End If

Next

For x = 1 To 60000

    If Mid(Range("T1:T60000").Cells(x), 6, 1) = "-" Then

        prefix = Mid(Range("T1:T60000").Cells(x), 1, 5)
    End If

            If Cells(x, Colnum + 1) <> "" And Len(Trim(Range("D1:D60000").Cells(x))) = 10 Then
             Cells(x, Colnum) = Cells(x, Colnum + 1)


            End If


Next


For x = 1 To 60000

    If Mid(Range("T1:T60000").Cells(x), 6, 1) = "-" Then

        prefix = Mid(Range("T1:T60000").Cells(x), 1, 5)
    End If

            If Cells(x, Colnum + 1) <> "" And Len(Trim(Range("D1:D60000").Cells(x))) = 11 Then
             Cells(x, Colnum) = Mid(Trim(Range("D1:D60000").Cells(x)), 1, 5) & Mid(Trim(Range("D1:D60000").Cells(x)), 7, 5)

            End If

Next


Range("V1") = "On Restricted List"
Range("W1") = "Export Variant"
Range("X1") = "On Promo List"

For x = 1 To 60000


If Cells(x, Colnum + 5) > 0 Then


    Range("V1:V60000").Cells(x) = "=IF(ISERROR(VLOOKUP(RC[-19],'Export Restricted List'!C[-21],1,FALSE,)"
    Range("W1:W60000").Cells(x) = "=IF(ISERROR(VLOOKUP(RC[-20],'Export Restricted List'!C[-22]:C[-17],5,FALSE),"""")"
    Range("X1:X60000").Cells(x) = "=IF(ISERROR(VLOOKUP(RC[-21],'Export Restricted List'!C[-22]:C[-17],6,FALSE),"""")"

End If

Next

End Sub
Posted
Updated 28-Jul-15 6:38am
v2
Comments
[no name] 28-Jul-15 10:44am
   
I am not at all familiar with any programming language called "Runtime" so I am assuming that you really mean VBA. http://answers.microsoft.com/en-us/office/forum/office_2007-excel/excel-vba-run-time-error-1004-application-defined/0211771e-8f9a-478a-9c61-ad00aaaaabfe?auth=1
Member 11869095 28-Jul-15 11:37am
   
I do mean VBA and I have searched the Microsoft Office site regarding the error. I am still missing something as I have either not found the solution or have not understood the solution. Thank you.
[no name] 28-Jul-15 11:45am
   
http://www.cpearson.com/Excel/DebuggingVBA.aspx

advice: Run your program with the debugger and check the variables and cells on every single line.

For your error message: you want to store a formula in a cell but
"=IF(ISERROR(VLOOKUP(RC[-19],'Export Restricted List'!C[-21],1,FALSE,)"
is not a formula, in fact none of your 3 lookup lines is a formula. More importantly, a cell is an object, not a variable.

Your program is weird. Have you ever made such a program in VBA for Excel ?

I see many places where your program will fail to do what you want, even if there is no error message.

You should take time to learn the proper way to store values and formulas in a cell. get a tutorial, get a sample program that store things in cells.

Correcting all the problems I see is a complete rewrite of the program, so either you find a tuto with specifics to VBA for excel, either you hire a programmer.
   
v2
Comments
Member 11869095 3-Aug-15 13:44pm
   
Thank you for your input, but all "formulas" are now working properly with minimal adjustments.
can you please check the excel sheet name .Excel name should be Paste Data Here hen only your code will work. I have tried your code Only thing is you might not have a sheetname as Paste Data Here.
   
Comments
Member 11869095 28-Jul-15 13:55pm
   
MJ2014, Thank you for looking. I have a worksheet called Paste Data Here and Export Restricted List referenced in the Vlookup. The macro executes fine until I get to the portion where it is supposed to add the Vlookups.
MJ2014 28-Jul-15 13:57pm
   
tell me exactly where you are getting error.
Member 11869095 28-Jul-15 15:54pm
   
5 lines above End Sub where I start try to put the Vlookups in.
Sub CheckOrderMacro()

Worksheets("Paste Order Data Here").Select

Dim Colnum As Integer
Dim prefix As String
Dim suffix As String
Dim x As Long
Dim RestrictLst As Variant
Dim LastRow As Long
Dim Sht As Worksheet


' Look at Export Restricted List to determine last row of data
' and create array based on A1 to A last row...

Set Sht = ThisWorkbook.Worksheets("Export Restricted List")
LastRow = Sht.Cells(Sht.Rows.Count, "A").End(xlUp).Row

'Set Array for Restricted List
RestrictLst = Range("'Export Restricted List'!A1:A" & LastRow).Value

'Move to the Paste Order Data Here worksheet to compare the case codes
'against the array RestrictLst

Set Sht = ThisWorkbook.Worksheets("Paste Order Data Here")

'The following code determines what column contains the case codes.
'Although the macro is reliant on the order data being pasted starting in column D.

Range("C1").Select
For x = 1 To 100

    ' It is essential that the header Case Code does not have additional spaces before, in between or after.
    If UCase(Cells(1, x)) = "CASE CODE" Then

        'The column to the left Case Code will hold the values to compare to the array.
        Colnum = x - 1
Exit For
    End If
Next

' Loops through the rows capturing the prefix of the case codes and suffix of the case codes
' and combining them to a full case code for comparison.
For x = 1 To 1000

    If Mid(Range("T1:T1000").Cells(x), 6, 1) = "-" Then
        
        prefix = Mid(Range("T1:T1000").Cells(x), 1, 5)
    End If
    
    If Cells(x, Colnum + 1) <> "" And Len(Trim(Range("D1:D1000").Cells(x))) = 5 Then
        Cells(x, Colnum) = CStr(prefix & Cells(x, Colnum + 1))
             
        If Mid(Range("D1:D1000").Cells(x), 6, 1) = "-" Then
            suffix = Mid(Range("D1:D1000").Cells(x), 7, 5)
            Cells(x, Colnum) = CStr(prefix & suffix)
        Else: Cells(x, Colnum) = CStr(prefix & Cells(x, Colnum + 1))
                
            If Range("D1:D1000").Cells(x) = "**********" Then
                Cells(x, Colnum) = CStr(Cells(x, Colnum + 1))
            End If
        End If
    
    End If

Next

For x = 1 To 1000

    If Mid(Range("T1:T1000").Cells(x), 6, 1) = "-" Then
        
        prefix = Mid(Range("T1:T1000").Cells(x), 1, 5)
    End If
    
    If Cells(x, Colnum + 1) <> "" And Len(Trim(Range("D1:D1000").Cells(x))) = 4 Then
        Cells(x, Colnum) = CStr(prefix & "0" & Cells(x, Colnum + 1))
    End If

Next

For x = 1 To 1000

    If Mid(Range("T1:T1000").Cells(x), 6, 1) = "-" Then
        
        prefix = Mid(Range("T1:T1000").Cells(x), 1, 5)
    End If
    
    If Cells(x, Colnum + 1) <> "" And Len(Trim(Range("D1:D1000").Cells(x))) = 10 Then
        Cells(x, Colnum) = Cells(x, Colnum + 1)
     End If

Next

For x = 1 To 1000

    If Mid(Range("T1:T1000").Cells(x), 6, 1) = "-" Then
        
        prefix = Mid(Range("T1:T1000").Cells(x), 1, 5)
    End If
    
    If Cells(x, Colnum + 1) <> "" And Len(Trim(Range("D1:D1000").Cells(x))) = 11 Then
        Cells(x, Colnum) = Mid(Trim(Range("D1:D1000").Cells(x)), 1, 5) & Mid(Trim(Range("D1:D1000").Cells(x)), 7, 5)
             
    End If

Next



For x = 1 To 1000

    Range("V1:V1000").Cells(x) = "=VLOOKUP(RC[-19],'Export Restricted List'!C[-21]:C[-21],1,FALSE)"
    Range("W1:W1000").Cells(x) = "=VLOOKUP(RC[-20],'Export Restricted List'!C[-22]:C[-15],5,FALSE)"
    Range("X1:X1000").Cells(x) = "=VLOOKUP(RC[-21],'Export Restricted List'!C[-23]:C[-15],6,FALSE)"
    Range("Y1:Y1000").Cells(x) = "=VLOOKUP(RC[-22],'Export Restricted List'!C[-24]:C[-15],7,FALSE)"

    
Next

Range("V1") = "On Restricted List"
Range("W1") = "Export Variant"
Range("X1") = "On Promo List"
Range("Y1") = "Restriction Begins"

Range("V2:Y500").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Selection.Replace What:="1/0/1900", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Application.CutCopyMode = False
    
Range("W2:Y500").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
Range("D1").Select
    
MsgBox ("Order Check is complete. Please review results.")

End Sub
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900