Using Set but Error 'Application-defined or object-defined error' shows up

Question

Sorry the question is not so specified. I've tried to use Set to help me filling a quelity control table, but the error 10o4 show up. It seems correct for me and so I don't know how to correct it. So please take a look of it, and leave a comment if you've got some idea. Thank you!

Private Sub FillTarget(ByVal TargetSheet As String, ByVal DepositSheet As String, _
        ByVal TargetRow As Integer, ByVal TargetColumn As String, _
        ByVal DepositRow As Integer, ByVal DepositColumn As Integer)

Dim i, j, k As Integer
Dim OpenedFile As String
Dim myObject As String

Dim MarkRow As Integer
MarkRow = 1
Dim myData As String
Dim EndSearch As Boolean

Dim TargetCell As Range
Dim TargetTag As Range
Dim TargetType As Range
Dim TargetZone As Range
Dim TargetTest As Range

Dim DepositTag As Range
Dim DepositZone As Range
Dim DepositResult As Range

For i = 3 To TargetRow
    With Worksheets(TargetSheet)
        myObject = .Cells(i, 15).Text + "_" + Worksheets(TargetSheet).Cells(i, 17).Text
        Set TargetCell = .Cells(i, TargetColumn) <==== Here comes the error
        Set TargetTag = .Cells(i, 15)
        Set TargetType = .Cells(i, 17)
        Set TargetZone = .Cells(i, 18)
        Set TargetTest = .Cells(i, 20)
    End With

    For j = MarkRow To DepositRow
        With Worksheets(DepositSheet)
            Set DepositTag = .Cells(j, 1)
            Set DepositZone = .Cells(j, 2)
        End With

        If InStr(DepositTag.Text, myObject) <> 0 Then
            OpenedFile = OpenedFile & DepositTag.Text & "|"

            If InStr(DepositZone.Text, TargetZone.Text + ":") <> 0 _
                Or InStr(TargetZone.Text, "/") <> 0 Then

                For k = 2 To DepositColumn
                    With Worksheets(DepositSheet)
                        Set DepositResult = .Cells(j, k)
                    End With

                    If InStr(DepositResult.Text, TargetTest.Text) <> 0 Then
                        MarkRow = j
                        myData = DepositResult.Text
                        'Split_monData
                        'Derniere_Colonne
                        TargetCell.Value = myData
                        EndSearch = True
                        Exit For
                    End If

                Next k

            End If

        End If

        If EndSearch Then Exit For
    Next j

    EndSearch = False
Next i
End Sub

just FYI, your code can be shortened a ton by placing your "Dim"s in one line. Instead of Dim range1 As Range new line Dim range2 As Range, they all can be written as Dim range1 As Range, range2 As Range. Important to note that you must always include the As Range (or whatever the object is), or that particular variable will be auto-declared as a Variant — RGA
– RGA, Commented Jul 6, 2016 at 8:48
To complete @RGA's comment, you should write Option Explicit above your Sub so you'll always have to declare your variables. Also, be careful when you declare Dim i, j, k As Integer only k is declared as integer, i and j will be as Variant you need to write Dim i As Integer, j As Integer, k As Integer — Rémi
– Rémi, Commented Jul 6, 2016 at 8:53
Nice to meet you again @Rémi! And for the Option Exolicit, could you please explain a little bit? I don't know why and how to use it. — Hiddenllyy
– Hiddenllyy, Commented Jul 6, 2016 at 8:55
@Hiddenllyy You just need to write Option Explicit in the first line of your module. This way if you are using a variable that you forgot to declare when you run the macro it won't work. link — Rémi
– Rémi, Commented Jul 6, 2016 at 9:05

RGA · Accepted Answer · 2016-07-06 09:04:57Z

1

The default value for Cells() is the value of the cell. Pass it as a Range to define the Range object variable.

Everywhere where you have a Range variable equal to .Cells(something), change it to .Range(.Cells(something).Address) and it should work as intended

Further, TargetColumn is declared as a string, rather than a numeric value. If the string value is not of a column name (ie "A" or "B"), this will error. Either pass the variable to a numeric value with Cdbl() CInt() or Clng(), or declare the variable with a numeric type from the start

edited Jul 6, 2016 at 9:04

answered Jul 6, 2016 at 8:43

RGA

2,60723 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hiddenllyy Over a year ago

Thank you, but it didn't work. I've changed .Cells(i,j) into .Range(Cells(i,j).Address), but still i met the error 1004.

RGA Over a year ago

@Hiddenllyy I see now it could be related to "TargetColumn" which is passed in as a string. Is this string value an uppercase letter representing a specific column?

Rémi Over a year ago

@RGA is right. TargetColumn is declared as String so if its values is supposed to be 1 you will get "1". You need to declare it as a numerical variable (integer, Long, etc) or as a Range and add .Column right after.

RGA Over a year ago

@Hiddenllyy alternatively, you can just add Cint(TargetColumn) which will pass the value as an integer, although this is not always the smartest way to do this. Declaring it numeric from the start would be the best

Collectives™ on Stack Overflow

Using Set but Error 'Application-defined or object-defined error' shows up

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related