Change first 2 charecters in cell to certain integer (with loop,VBA)

[flay]

I was trying to create a script in vba which check the cell "A1" and replaces the first 2 charecters of the string to a integer if the first 2 characters are Ni lets say then change them to 801 . should look something like that:

originaltext = Range("a1").Value

If (Left(A1, 2) = "NI") Then newtext = Replace("originaltext", "NI", "801")

ElseIf (Left(A1, 2) = "RE") Then newtext = Replace("originaltext", "RE", "821")

ElseIf (Left(A1, 2) = "NV") Then newtext = Replace("originaltext", "NV", "571")

ElseIf (Left(A1, 2) = "NF") Then newtext = Replace("originaltext", "NF", "831")

end if n=n+1 Loop

i want it to go trough every cell and do that action starting from a1 lets say all the way down to a2 and a3 and so on.

how do i create a right loop for that?

 

[Fluff]

Hi & welcome to MrExcel.
How many different strings do you need to check for?

 

[flay]

Hi & welcome to MrExcel.
How many different strings do you need to check for?

i have a very big database, all of the cells from A1 to somewhere around A130000 starts with one of these charecters : "NI" , "RE", "NV", "NF"
i need to check which one they start with and Replace them with a certain integer as mentioned in the code.
NI=801
RE=821
NV=571
NF=831

 

[Fluff]

So you just need to check for those 4 strings?

 

[flay]

So you just need to check for those 4 strings?

Yes 4 strings , check for them and Replace them

 

[razzandy]

Sound like a case for: 'Case Select' Fluff. Thanks to you and your help in the past I use it a lot now

 

[Fluff]

Ok, how about

Sub flay()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("NI", "801", "RE", "821", "NV", "571", "NF", "831")
   For i = 0 To UBound(Ary) Step 2
      With Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Value = Evaluate(Replace("if(left(@,2)=""" & Ary(i) & """," & Ary(i + 1) & "&mid(@,3,100),@)", "@", .Address))
      End With
   Next i
End Sub

 

[flay]

Ok, how about

Sub flay()
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Array("NI", "801", "RE", "821", "NV", "571", "NF", "831")
   For i = 0 To UBound(Ary) Step 2
      With Range("A2", Range("A" & Rows.Count).End(xlUp))
         .Value = Evaluate(Replace("if(left(@,2)=""" & Ary(i) & """," & Ary(i + 1) & "&mid(@,3,100),@)", "@", .Address))
      End With
   Next i
End Sub

that
works
perfectely..
im stunned thank you so much !
What if when it goes trough the row and searches for one of the 4 integers if it doesnt find a match it deletes the row .
is that possible?

 

[Fluff]

That's a totally different question, so needs a new thread. Thanks