Runtime error 1004 Application defined or object defined error

Question

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.

VB

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 28-Jul-15 4:29am

Member 11869095

Updated 28-Jul-15 5:38am

v2

Add a Solution

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

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Patrice T · Answer 1 · 2015-07-29T13:19:00

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

VB

"=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.

MJ2014 · Answer 2 · 2015-07-28T07:44:00

Solution 1

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.

Posted 28-Jul-15 7:44am

MJ2014

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.

Member 11869095 · Answer 3 · 2015-08-03T07:46:00

VB

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

Runtime error 1004 Application defined or object defined error

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0