Range Issue

[Darren Smith]

This code is to find the array Transit in column E then replace it but for some reason, the rng variable and FirstAddress remain blank?
I could send the workbook if that helps?

Option Explicit
Sub Vehicle_Name_Replace()

    Dim FirstAddress As String
    Dim MyArr As Variant
    Dim ws As Worksheet
    Dim rng As Range, DRng As Range
    Dim x As Long
    Dim i As Long
    Dim VModel As ComboBox

Set VModel = Body_And_Vehicle_Type_Form.Model_Type

Set ws = ThisWorkbook.Worksheets("Job Card Master")

    MyArr = Array("Transit")
    
    With ws.Range("E:E")

            For i = LBound(MyArr) To UBound(MyArr)


            Set rng = .Find(What:=MyArr(i), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=True)

            If Not rng Is Nothing Then
            
            FirstAddress = rng.Address
                Do
                    
                   Set DRng = rng
                    
                Select Case VModel.Value
     
                    Case ("Sprinter")
                    DRng.Value = "Sprinter"
                    
                    Case ("Master")
                    DRng.Value = "Master Movano NV400"
                    
                    Case ("Movano")
                    DRng.Value = "Master Movano NV400"
                    
                    Case ("NV400")
                    DRng.Value = "Master Movano NV400"
                    
                    Case ("Boxer")
                    DRng.Value = "Boxer Ducato Relay"
                    
                    Case ("Ducato")
                    DRng.Value = "Boxer Ducato Relay"
                    
                    Case ("Relay")
                    DRng.Value = "Boxer Ducato Relay"
                    
                    End Select
            Set rng = .FindNext(rng)
                Loop While Not rng Is Nothing And rng.Address <> FirstAddress
            End If
            
            Next i
            
    End With

End Sub

 

[Darren Smith]

Sorry thought it worked but it didn`t there is still a code issue to sort?

Loop While Not Rf Is Nothing And Rf.Address <> FirstAddress
The code above says "Block Variable not set"

 

[Alex Blakenburg]

Sorry thought it worked but it didn`t there is still a code issue to sort?

Loop While Not Rf Is Nothing And Rf.Address <> FirstAddress
The code above says "Block Variable not set"

Interesting. I can see the code you used on the Microsoft Range.FindNext method page but it doesn't actually seem to work.

Please change these lines, currently
(The address test is erroring out when Rf is Nothing)

             Set rng = .FindNext(rng)
        Loop While Not Rf Is Nothing And Rf.Address <> FirstAddress

And replace them with this:-

                Set Rf = .FindNext(Rf)
                If Rf Is Nothing Then Exit Do
              
            Loop While Rf.Address <> FirstAddress

 

[Darren Smith]

Thanks now work`s every time brilliant job

 

[Alex Blakenburg]

Thank you. I'm glad we got there in the end.

 

[RoryA]

I can see the code you used on the Microsoft Range.FindNext method page but it doesn't actually seem to work.

Yes, that has always been wrong.