lundi 20 avril 2015

Find and Replace in VBA

I am attempting to go through a column of department names (strings) and anything that contains the string " - INACTIVE" needs to have it deleted and just have the department name. For instance, if I had "Jacksonville - INACTIVE", I would want it to simply be "Jacksonville" in the column cell. I have attempted to use the .Find and .Replace methods but I don't seem to grasp the way the way to use them.

I have attempted it with help from a friend and this is what i have so far. I am new to this language.

 Sub DeleteRows()

    Dim c As Range
    Dim SrchRng
    Dim InactiveDepartment As String
    Dim Department As String

 Range("A1").Select

 InactiveDepartment = ActiveCell.Value

 Set SrchRng = ActiveSheet.UsedRange

 Do Until ActiveCell.Value = ""
     Set c = SrchRng.Find("INACTIVE", LookIn:=xlValues)
     If Not c Is Nothing Then
         Department = Replace(InactiveDepartment, " - INACTIVE", "")
         ActiveCell.Value = Department
         ActiveCell.Offset(1, 0).Select
    Else
         ActiveCell.Offset(1, 0).Select
    End If
Loop

End Sub

Aucun commentaire:

Enregistrer un commentaire