mardi 6 octobre 2020

method find of object range failed in vba

I have written a code that finds all the dye word and sum all the dye word value.

Here is the code

Dim name As String
name = "dye"

Dim findDyeRange As Range
Set findDyeRange = Range("Q10:S61")

Set firstDyeWord = findDyeRange.Find(name)

If firstDyeWord Is Nothing Then
    msgbox "nothing found"
Else
    firstDyeValue = firstDyeWord.Offset(0, 1).Value
    Set secondDyeWord = findDyeRange.FindNext(firstDyeWord)
    
    If secondDyeWord.Address = firstDyeWord.Address Then
        MsgBox firstDyeValue
        Exit Sub
    Else
        secondDyeValue = secondDyeWord.Offset(0, 1).Value
        Set thirdDyeWord = findDyeRange.FindNext(secondDyeWord)
        
        If thirdDyeWord.Address = firstDyeWord.Address Then
            MsgBox firstDyeValue + secondDyeValue
            Exit Sub
        Else
            thirdDyeValue = thirdDyeWord.Offset(0, 1).Value
            Set fourthDyeWord = findDyeRange.FindNext(thirdDyeWord)
            
            If fourthDyeWord.Address = firstDyeWord.Address Then
                MsgBox firstDyeValue + secondDyeValue + thirdDyeValue
                Exit Sub
            Else
                fourthDyeValue = fourthDyeWord.Offset(0, 1).Value
                Set fifthDyeWord = findDyeRange.FindNext(fourthDyeWord)
                
                If fifthDyeWord.Address = firstDyeWord.Address Then
                    MsgBox firstDyeValue + secondDyeValue + thirdDyeValue + fourthDyeValue
                    Exit Sub
                Else
                    fifthDyeValue = fifthDyeWord.Offset(0, 1).Value
                    Set sixthDyeWord = findDyeRange.FindNext(fifthDyeWord)
                    
                    If sixthDyeWord.Address = firstDyeWord.Address Then
                        MsgBox firstDyeValue + secondDyeValue + thirdDyeValue + fourthDyeValue + fifthDyeValue
                        Exit Sub
                    Else
                        sixthDyeValue = sixthDyeWord.Offset(0, 1).Value
                        MsgBox firstDyeValue + secondDyeValue + thirdDyeValue + fourthDyeValue + fifthDyeValue + sixthDyeValue
                    End If
                End If
            End If
        End If
    End If
End If

the code runs well. But when I removes the msgbox and set a code then it throws an error.

I want this code

If firstDyeWord Is Nothing Then
        Range("A9").value = 7

But it throws error "method find of object range failed in vba"

Help Please!

Aucun commentaire:

Enregistrer un commentaire