CODEWITHSUNDEEP
excelvba
Rotomegax
Rotomegax

Reputation: 27

Excel VBA error on searching and paste script

I'm making the excel VBA script that will copy and paste value on the first sheet ̣̣(Input) to the data storage sheet (DATA). If the update flag on J4 cells on Input sheet turned to 1, it will search the cell of DATA sheet that contain value on K4 cells of Input sheet and paste value to it. Half of the objectives have been completed, but when writing the script to find and paste value to the cells that have value of K4 cells it called

error 1004: Application-defined of object-defined error

on the PasteSpecial row of the script.

My script is here:

Sub add_data()
Dim DATA As Worksheet
Dim Input As Worksheet
Dim otk As Range
Set D = ThisWorkbook.Sheets("DATA")
Set I = ThisWorkbook.Sheets("Input")
L = 1
While L = 1
    If I.Range("J4").Value = 0 Then
        I.Range("K4:UX4").copy
        lastrow = D.Range("B1")
        D.Range("A" & lastrow).PasteSpecial Paste:=xlPasteValues, _
                                                    Operation:=xlPasteSpecialOperationNone, _
                                                    SkipBlanks:=False
        L = 0
    Else

        With D.Range("A1:A10000")
            Set otk = .Find(I.Range("K4").Value, LookIn:=xlValues)
            If Not otk Is Nothing Then
                I.Range("J4").Value = 0
            Else
                I.Range("K4:UX4").copy
                D.Range(otk).PasteSpecial Paste:=xlPasteValues, _
                                                                 Operation:=xlPasteSpecialOperationNone, _
                                                                 SkipBlanks:=False
                L = 0
            End If
        End With
    End If
Wend
End Sub

Upvotes: 0

Views: 47

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The problem is here

Set otk = .Find(I.Range("K4").Value, LookIn:=xlValues)
If Not otk Is Nothing Then
    ' runs when otk is something
    I.Range("J4").Value = 0
Else
    ' runs when otk is nothing
    I.Range("K4:UX4").copy
    D.Range(otk).PasteSpecial Paste:=xlPasteValues, _
                                                     Operation:=xlPasteSpecialOperationNone, _
                                                     SkipBlanks:=False
    L = 0
End If

Otherwise otk is nothing when you go into the Else part. Also otk is a range object and not an address so you need to use it as otk.PasteSpecial.

So you need to change If Not otk Is Nothing Then into If otk Is Nothing Then

Set otk = .Find(I.Range("K4").Value, LookIn:=xlValues)
If otk Is Nothing Then
    ' runs when otk is nothing
    I.Range("J4").Value = 0
Else
    ' runs when otk is something
    I.Range("K4:UX4").copy
    otk.PasteSpecial Paste:=xlPasteValues, _
                     Operation:=xlPasteSpecialOperationNone, _
                     SkipBlanks:=False
    L = 0
End If

Alternatively you can switch what is in the Else and in the If part.

Set otk = .Find(I.Range("K4").Value, LookIn:=xlValues)
If Not otk Is Nothing Then
    ' runs when otk is something
    I.Range("K4:UX4").copy
    otk.PasteSpecial Paste:=xlPasteValues, _
                     Operation:=xlPasteSpecialOperationNone, _
                     SkipBlanks:=False
    L = 0
Else
    ' runs when otk is nothing
    I.Range("J4").Value = 0
End If

Upvotes: 0

Related Questions