CODEWITHSUNDEEP
excelvba
Hiddenllyy
Hiddenllyy

Reputation: 179

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

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

Upvotes: 0

Views: 84

Answers (1)

RGA
RGA

Reputation: 2607

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

Upvotes: 1

Related Questions