PDA

View Full Version : [SOLVED] Add missing text in certain cells



Robert87
11-29-2016, 03:59 AM
Hello.

I have a spreadsheet where I want to find certain cells and add text to them.


Every week I get a workbook sent to me by a colleague of mine.
All cells in the C columns should contain the ID number of a drawing. The ID numbers look like this: M3TR-1***.
Sometimes when I get the workbook, the creator have forgotten to add the "M3TR-" part, and have just written "1***".

I´ve been trying to find a way to locate all cells in the B column which contains this error and correct it.

Could anyone of you fine experts help me with this?

Example:
B5 contains 1208.
I want B5 to be changed to M3TR-1208

B6 contains 1162
I want B6 to be changed to M3TR-1162

mancubus
11-29-2016, 04:27 AM
?


Sub vbax_57854_add_missing_pref()

Dim i As Long

With Worksheets("Sheet1") 'change Sheet1 to suit
For i = 2 To Range("B" & .Rows.Count).End(xlUp).Row
If Left(.Range("B" & i).Value, 5) <> "M3TR-" Then
.Range("B" & i).Value = "M3TR-" & .Range("B" & i).Value
End If
Next i
End With

End Sub

Robert87
11-29-2016, 05:01 AM
That does the trick, but it also creates a new problem.

That scrips adds M3TR- to all cells, even empty ones. I might have forgotten to say that not all cells are filled with data. There are some empty cells scattered around in the column.

mancubus
11-29-2016, 05:31 AM
Sub vbax_57854_add_missing_pref()

Dim i As Long

With Worksheets("Sheet1") 'change Sheet1 to süit
For i = 2 To Range("B" & .Rows.Count).End(xlUp).Row
If Len(Trim(.Range("B" & i).Value)) > 0 Then
If Left(.Range("B" & i).Value, 5) <> "M3TR-" Then
.Range("B" & i).Value = "M3TR-" & .Range("B" & i).Value
End If
End If
Next i
End With

End Sub

Robert87
11-29-2016, 05:43 AM
That does exact what I needed it to.

Thank you very much!

mancubus
11-29-2016, 07:32 AM
you are welcome.

please mark the thread as solved from threadtools dropdown for future references.