CODEWITHSUNDEEP
vbaexcel
Erin
Erin

Reputation: 147

If cell value starts with a specific set of numbers, replace data

My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.)

If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are the first numbers, replace entire cell value with "790")

I know there's a way to use an asterick to use only part of the cell value but I'm not 100% on the syntax. This is the current code I have, but it's searching for specifically "###*", not values that start with "###". Any and all help is greatly appreciated!

lastRow = Range("A" & Rows.Count).End(xlUp).Row
colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0)
For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum))  
If c.Value = "614*" _
        Or c.Value = "626*" _
        Or c.Value = "618*" _
        Or c.Value = "609*" _
        Or c.Value = "605*" Then
            c.Value = "737"

`

Upvotes: 8

Views: 28136

Answers (3)

Dan Donoghue
Dan Donoghue

Reputation: 6216

Here is my take on the problem:

Sub SO()
    Dim MyString As String
    MyString = "614,626,618,609,605"
    For X = 1 To Range("C" & Rows.Count).End(xlUp).Row
        If Replace(MyString, Left(Range("C" & X).Value, 3), "") <> MyString Then Range("C" & X).Value = "737"
    Next
End Sub

Upvotes: 0

brettdj
brettdj

Reputation: 55692

Better to do a range replace rather than loop through each cell for speed:

Dim rng1 As Range
Dim LastRow As Long
Dim ColNum As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
ColNum = Application.Match("Number", Range("A1:CC1"), 0)
On Error GoTo 0

If Column Is Nothing Then Exit Sub

Set rng1 = Range(Cells(2, ColNum), Cells(LastRow, ColNum))
With rng1
    .Replace "626*", "727", xlWhole
    .Replace "618*", "727", xlWhole
    .Replace "609*", "727", xlWhole
    .Replace "737*", "727", xlWhole
End With

Upvotes: 3

FreeMan
FreeMan

Reputation: 5687

Use the LEFT() function, as shown below:

lastRow = Range("A" & Rows.Count).End(xlUp).Row
colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0)
For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum))  
  If LEFT(c.Value,3) = "614" _
     Or LEFT(c.Value,3) = "626" _
     Or LEFT(c.Value,3) = "618" _
     Or LEFT(c.Value,3) = "609" _
     Or LEFT(c.Value,3) = "605" Then
    c.Value = "737"

Upvotes: 9

Related Questions