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]

We are getting there the only problem is it replaces the whole line rather than just the Vehicle name.

These are some examples of what I mean
Subframe - See Drawing (Transit)
Topframe - See Drawing (Transit)
Gantry - See Drawing (Transit)
Just need the Transit vehicle name to change to Combobox value

 

[Marc L]

​

So the Find method issue is solved …​

 

[Darren Smith]

​

So the Find method issue is solved …​

Except it does not find the first row with Transit in it. But finds the rest??

 

[Marc L]

​

In order I can give it a try so according to your attachment what is the cell address you are expecting as the first one ?​

 

[Darren Smith]

Starts at E14

 

[Marc L]

According to your attachment this VBA demonstration well works on my side :​

Sub Demo1()
           Dim Rf As Range
    With Sheet7.UsedRange.Columns(5)
           Set Rf = .Find("Transit", , xlValues, xlPart)
        If Not Rf Is Nothing Then
            Debug.Print Rf.Address
            Application.Speech.Speak "Found in cell " & Rf.Address(False, False)
            Set Rf = Nothing
        End If
    End With
End Sub

 

[Darren Smith]

Dim Rf As Range With Sheet7.UsedRange.Columns(5) Set Rf = .Find("Transit", , xlValues, xlPart) If Not Rf Is Nothing Then Debug.Print Rf.Address Application.Speech.Speak "Found in cell " & Rf.Address(False, False) Set Rf = Nothing

It now says Rf Range is Nothing??

My Lastest attempt is below.

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]

​

It well works on my side according to your initial attachment but not in your last one as you have changed the rules :​

look what happens with so badly and not necessary merging cells ‼​

​

So logically just change the source range for columns "E:F" rather than just column #5 and it works … ​

 

[Darren Smith]

It`s found the first cell with Transit but won`t change the value.
There is the word Transit in various cells in Column E down to the end of the workbook.

 

[Darren Smith]

Don't worry about the comment above

We are getting there the only problem is it replaces the whole line rather than just the Vehicle name.
These are some examples of what I mean just change Transit to Combobox value not the rest of the values
Subframe - See Drawing (Transit)
Topframe - See Drawing (Transit)
Gantry - See Drawing (Transit)