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

 

[Joe4]

I believe your issue may be with this part of your FIND command:

After:=.Cells(.Cells.Count)

I think that is telling to start searching AFTER the last cell in your range, in which case it probably wouldn't find anything!
You probably want to tell it to start at E1, the first cell in your range.

 

[Darren Smith]

What should I do just delete it or replace it with something else?

 

[Joe4]

What should I do just delete it or replace it with something else?

I went back and edited my original reply, so perhaps you did not see my second line the first time around.

 

[Darren Smith]

I`ve changed it to how you said it should be. Still, no joy on the ranges they always say Nothing?
Could I send the workbook please for you to have a look at?

 

[Marc L]

Hi,​

​

I totally disagree with post #2 as it's very not a concern with the Range.Find method as after the last cell it starts from the first cell.​

Each time this method does not find anything the error comes from at least one of its argument …​

So easy to find out with an attachment !​

 

[steve the fish]

Yup pretty sure that if you use after cells.count that actually means start at the first cell. If you start after the first cell you start at the 2nd.

 

[NoSparks]

Have a look at the section "Using After with Find" on this page

 

[Darren Smith]

So what is the problem?

Here is my Workbook to make it easy to sort

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[Marc L]

​

As I explained it's just a bad argument as usual !​

Your codeline searches for 'Transit' as the whole content of a cell but in fact you need to search for a partial match ‼​

As Find always works so when it's not the case it just the logic used which is failing …​

So first try xlPart rather than xlWhole …​